(高分求助)两个比较难的SQL2000的SQL语句查询问题,非专家莫入!
发布网友
发布时间：2022-04-28 15:38
共13个回答
热心网友
时间：2022-05-05 08:11
create table temp_znb_1208_practice(name varchar(10),course varchar(20),score numberic)
insert into temp_znb_1208_practice values('张三','语文',74)
insert into temp_znb_1208_practice values('张三','数学',83)
insert into temp_znb_1208_practice values('张三','物理',93)
insert into temp_znb_1208_practice values('李四','语文',74)
insert into temp_znb_1208_practice values('李四','数学',84)
insert into temp_znb_1208_practice values('王五','语文',74)
insert into temp_znb_1208_practice values('王五','数学',83)
insert into temp_znb_1208_practice values('王五','物理',93)
insert into temp_znb_1208_practice values('王五','历史',100)
select (select count(*) from temp_znb_1208_practice b where b.name=a.name and b.score<=a.score) 序列,* from temp_znb_1208_practice a
1 张三 语文 74.00
2 张三 数学 83.00
3 张三 物理 93.00
1 李四 语文 74.00
2 李四 数学 84.00
1 王五 语文 74.00
2 王五 数学 83.00
3 王五 物理 93.00
4 王五 历史 100.00
第二题先增加一列来做辅助,或者可以另外创建临时表
create table temp_znb_1208_practice_2(name varchar(10),enroll_time varchar(20))
insert into temp_znb_1208_practice_2 values('张三', '2008-01-01')
insert into temp_znb_1208_practice_2 values('张三', '2008-01-20')
insert into temp_znb_1208_practice_2 values('张三', '2008-05-30')
insert into temp_znb_1208_practice_2 values('李四', '2008-04-05')
insert into temp_znb_1208_practice_2 values('李四', '2008-06-08')
--增加列
alter table temp_znb_1208_practice_2 add enroll_times varchar(20)
--更新第一次报到
update temp_znb_1208_practice_2
set enroll_times = '第一次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name
group by b.name)
--更新第二次报到
update temp_znb_1208_practice_2
set enroll_times='第二次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name
and enroll_time not in
(select min(b.enroll_time) from temp_znb_1208_practice_2 b where name=b.name group by b.name)
group by b.name)
--更新第三次报到
update temp_znb_1208_practice_2
set enroll_times='第三次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name
and enroll_time not in
(select min(b.enroll_time) from temp_znb_1208_practice_2 b where name=b.name group by b.name)
and enroll_time not in
(select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name and enroll_time not in
(select min(b.enroll_time) from temp_znb_1208_practice_2 b where name=b.name group by b.name)
group by b.name)
group by b.name)
--以上3个更新，每个更新的条件和前一个更新有关，去掉前几个时间后求的最小时间就是需要更新的时间。也许还有更好的更新方法，我的方法比较笨
select * from temp_znb_1208_practice_2
--更新结果
张三 2008-01-01 第一次报到
张三 2008-01-20 第二次报到
张三 2008-05-30 第三次报到
李四 2008-04-05 第一次报到
李四 2008-06-08 第二次报到
/*
我想到了更简单的更新方法，比上面一个容易理解，逐行更新，代码如下
--先将标识列enroll_times的值设为null
update temp_znb_1208_practice_2
set enroll_times='null'
--逐行更新
update temp_znb_1208_practice_2
set enroll_times = '第一次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name and enroll_times='null'
group by b.name)
update temp_znb_1208_practice_2
set enroll_times = '第二次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name and enroll_times='null'
group by b.name)
update temp_znb_1208_practice_2
set enroll_times = '第三次报到' where enroll_time in (select min(b.enroll_time) from temp_znb_1208_practice_2 b
where name=b.name and enroll_times='null'
group by b.name)
*/
--交叉表转换
select name,
isnull(max(case enroll_times when '第一次报到' then enroll_time end ),'') '第一次报到',
isnull(max(case enroll_times when '第二次报到' then enroll_time end ),'') '第二次报到',
isnull(max(case enroll_times when '第三次报到' then enroll_time end ),'') '第三次报到'
from temp_znb_1208_practice_2
group by name
order by name desc
--最后结果
张三 2008-01-01 2008-01-20 2008-05-30
李四 2008-04-05 2008-06-08
------终于做完了，好累啊，哈哈(码字很辛苦的！)
--另外再送上第二题的oracle做法,用分析函数，一句话就出来了
select name,enroll_time, row_number() over (partition by "NAME" order by enroll_time) '次数' from temp_znb_1208_practice_2;
是不是感觉oracle比sql server强大很多呢？
热心网友
时间：2022-05-05 09:29
上面很多人说得都很有道理，但略显复杂，又是游标又是循环的。
第一题:
wewewe7的方法很好很强大,借用下,加了句order by
select 行号=(select count(*) from 表 b where b.姓名=a.姓名 and b.分数<=a.分数),* from 表 a order by 姓名,分数
第二题:
如法炮制，加一列次数放到临时表temptable,然后就拼接SQL搞定。
select 次数=(select '第'+cast(count(*) as varchar(2))+'次报到' from 表 b where b.姓名=a.姓名 and datediff(day,b.报到日期,a.报到日期)>=0),* into temptable from 表 a
order by 姓名,报到日期
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 次数 when ''' + 次数 + ''' then 报到日期 else 0 end) [' + 次数 + ']'
from (select distinct 次数 from temptable) as a
set @sql = @sql + ' from temptable group by 姓名'
exec(@sql)
热心网友
时间：2022-05-05 11:03
第一个：
查询人员表得到数据集合 A
开始循环A
通过A里面的人员信息得到每个人成绩单，且这个成绩单按order by 姓名, 课程, 分数 排序查询数据得到B，同时初始化一个变量P用来保存行号
开始循环B,将信息 行号 姓名 课程 分数加入到临时表，循环的同时行号加1
----
A是外循环，B是内循环，P的初始化在A循环内，每次循环都进行初始化。
第二个：
和第一个一样，两个循环嵌套，子循环中每次都要记录报道时间的次序，并通过这个次序把对应的时间插入到对应的列。
---------
之前需要先得到个人报道次数最多的数值，通过这个数值建立临时表
你可能需要用到
Execute sp_ExecuteSql
例子：
Declare @Sqlchar nvarchar(200)
Set @Sqlchar = 'Select * from AAA'
Execute sp_ExecuteSql @Sqlchar
效果和直接Select * from AAA一样，但这样操作可以让你动态地制定列
---
参考资料：只是思路，采不采用由你
热心网友
时间：2022-05-05 12:55
insert into b values('张三','语文','74');
insert into b values('张三','数学','83');
insert into b values('张三','物理','93');
insert into b values('李四','语文','74');
insert into b values('李四','数学','84');
insert into b values('王五','语文','74');
insert into b values('王五','数学','83');
insert into b values('王五','物理','93');
insert into b values('王五','历史','100')
select 行号=(select count(*) from b where b.姓名=c.姓名 and b.分数<c.分数)+1 ,* from b c
drop table b
create table a(姓名 varchar(4),报道日期 datetime)
insert into a values('张三','2008-01-01');
insert into a values('张三','2008-01-20');
insert into a values('张三','2008-05-30');
insert into a values('李四','2008-04-05');
insert into a values('李四','2008-06-08')
declare @sql varchar(8000)
set @sql='select 姓名'
select @sql = @sql +' , max(case pc when ''' + pc + ''' then 报道日期 else 0 end) [第' + pc+ '次报道]'
from (select distinct (select cast(count(*)+1 as varchar(1)) from a where a.姓名=c.姓名 and a.报道日期<c.报道日期) as pc from a c) as s
set @sql = @sql + ' from (select * ,pc=(select count(*)+1 from a where a.姓名=c.姓名 and a.报道日期<c.报道日期)from a c) as e group by 姓名'
exec(@sql)
drop table a
第2题有点问题，要是日期用VARCHAR则会报错“varchar转换为int语法错误”
用datetime类型的话，会填上1900-01-01 00:00:00.000
热心网友
时间：2022-05-05 15:03
select 行号=(select count(*)+1 from 表 b where b.姓名=a.姓名 and b.分数<=a.分数),* from 表 a order by 姓名,分数,行号
第二个问题需要用到游标进行判断，也是先生成一个临时表，最后select临时表就oK了或者你可以利用嵌套查询把所有记录叠加，如（报到日期=第一次查询到的记录+'$'+第二次查询到的记录等等
楼上的回答正确，就是第一个在cout（*）后+1还有order by 姓名,分数改成order by 姓名,分数,行号 这样更好
热心网友
时间：2022-05-05 17:27
这样得出的结果，李四的数学，和英语的id都会是3
所以用cour（课程）字段排序不会出现重复值，
create table t11 (
nam varchar (20),
cour varchar (20),
score int
)
go
insert into t11 values ('张三' ,'语文', 74 )
insert into t11 values ('张三' ,'英语', 74 )----特意加上有相同分数的
insert into t11 values ('张三', '数学', 83 )
insert into t11 values ('张三', '物理', 93 )
insert into t11 values ('李四', '语文', 74 )
insert into t11 values ('李四', '数学', 84 )
insert into t11 values ('王五', '语文', 74 )
insert into t11 values ('王五', '数学', 83 )
insert into t11 values ('王五', '物理', 93 )
insert into t11 values ('王五', '历史', 100 )
go
select (select count(*) from t11 as t2
where t1.nam=t2.nam and t1.cour<=t2.cour) id,
nam ,cour, score from t11 as t1
order by nam,cour desc
---------------------------------
1 李四 语文 74
2 李四 数学 84
3 李四 英语 84
1 王五 语文 74
2 王五 物理 93
3 王五 数学 83
4 王五 历史 100
1 张三 语文 74
2 张三 物理 93
3 张三 数学 83
----------------------------------
--第二个问题是这样：
create table t2(name varchar(10),e_time varchar(20))
insert into t2 values('张三', '2008-01-01')
insert into t2 values('张三', '2008-01-20')
insert into t2 values('张三', '2008-05-30')
insert into t2 values('张三', '2008-06-30')----多插入几行
insert into t2 values('张三', '2008-07-30')
insert into t2 values('李四', '2008-04-05')
insert into t2 values('李四', '2008-06-08')
alter table t2 add keycol int
update t2
set keycol=(select count(*) from t2 as t where t.name=t2.name
and t.e_time<=t2.e_time)
from t2 as t2;
----group by 的话要用聚合函数，所以用max不会影响结果
select * from t2
select name,max(case when keycol=1 then e_time end) as '第一次',
max(case when keycol=2 then e_time end) as '第二次',
max(case when keycol=3 then e_time end) as '第三次',
max(case when keycol=4 then e_time end) as '第四次',
max(case when keycol=5 then e_time end) as '第五次'
from t2
group by name
---结果：
name 第一次 第二次 第三次 第四次 第五次
李四 2008-04-05 2008-06-08 NULL NULL NULL
张三 2008-01-01 2008-01-20 2008-05-30 2008-06-30 2008-07-30
热心网友
时间：2022-05-05 20:09
怕费事做完了没有分。
想要答案的话HI我就行了。
-------------
create table t1 (
nam varchar (20),
cour varchar (20),
score int
)
go
insert into t1 values ('张三' ,'语文', 74 )
insert into t1 values ('张三', '数学', 83 )
insert into t1 values ('张三', '物理', 93 )
insert into t1 values ('李四', '语文', 74 )
insert into t1 values ('李四', '数学', 84 )
insert into t1 values ('王五', '语文', 74 )
insert into t1 values ('王五', '数学', 83 )
insert into t1 values ('王五', '物理', 93 )
insert into t1 values ('王五', '历史', 100 )
go
SELECT (SELECT COUNT(*) FROM t1 B
WHERE B.nam = A.nam AND B.score <= A.score) as ord,*
FROM t1 A
ORDER BY A.nam, A.score
------------
create table t2(name varchar(10),enroll_time varchar(20))
go
insert into t2 values('张三', '2008-01-01')
insert into t2 values('张三', '2008-01-20')
insert into t2 values('张三', '2008-05-30')
insert into t2 values('李四', '2008-04-05')
insert into t2 values('李四', '2008-06-08')
go
create FUNCTION f_str(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + enroll_time FROM t2 WHERE name=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt name, enter_time= dbo.f_str(name) FROM t2 GROUP BY name
drop table t2
drop function dbo.f_str
--PS：这确实是两道很经典的题目
--我不怕垫底，再说明一下根本就没有必要考虑分数重复的情况因实际的设计
--表的时修一定会用一个唯一不重复的主键ID的所以只要把B.score <= A.score
--换成a.id<=b.id就行了。不知这样说对不对呢。
热心网友
时间：2022-05-05 23:07
select 行号=(select count(1) from 表 b where b.姓名=a.姓名 and b.分数<=a.分数),* from 表 a
第二个有点难，不太会，希望对你有帮助
热心网友
时间：2022-05-06 02:21
貌似答案已经出来了哦。不错不错。
热心网友
时间：2022-05-06 05:53
热心网友
时间：2022-05-06 09:41
热心网友
时间：2022-05-06 13:45
热心网友
时间：2022-05-06 18:07
