求SQL:log表中每天得分最高的前10名
我前一个帖子是问怎么取一天历史记录的前10名,Limpire(昨夜小楼) 已经帮我解答了,再此谢谢他~
这次我又遇见个难题,就是怎么取log表中每天的前10名~
要求,每天的前10名,只取本人(userId)最好的成绩
表结构和数据如下:
logId userId userResult logInsertDate
240 28 320 2007-9-5 9:20:28
254 35 170 2007-9-5 10:24:11
121 24 150 2007-8-27 14:13:38
122 25 90 2007-8-27 14:13:48
136 26 40 2007-8-27 14:22:34
138 27 490 2007-8-27 14:23:39
140 28 490 2007-8-27 14:26:26
175 24 400 2007-8-27 15:03:04
189 28 420 2007-8-27 15:23:58
226 29 400 2007-8-27 16:49:31
230 29 400 2007-8-28 16:49:31
245 30 400 2007-8-27 16:49:31
246 31 400 2007-8-27 12:49:31
247 32 400 2007-8-27 16:49:31
248 33 400 2007-8-27 16:49:31
398 34 11 2007-8-27 16:55:31
318 28 590 2007-9-7 10:44:01
386 28 780 2007-9-7 12:20:29
=====================================================
结果：
logId userId userResult logInsertDate
138 27 490 2007-8-27 14:23:39
140 28 490 2007-8-27 14:26:26
246 31 400 2007-8-27 12:49:31
175 24 400 2007-8-27 15:03:04
226 29 400 2007-8-27 16:49:31
245 30 400 2007-8-27 16:49:31
247 32 400 2007-8-27 16:49:31
248 33 400 2007-8-27 16:49:31
122 25 90 2007-8-27 14:13:48
136 26 40 2007-8-27 14:22:34
230 29 400 2007-8-28 16:49:31
240 28 320 2007-9-5 9:20:28
254 35 170 2007-9-5 10:24:11
386 28 780 2007-9-7 12:20:29
[解决办法]
/*
Limpire：宽带出问题，现在才恢复
*/
--原始数据：@T_LOG
declare @T_LOG table(logId int,userId int,userResult int,logInsertDate datetime)
insert @T_LOG
select 240,28,320, '2007-9-5 9:20:28 ' union all
select 254,35,170, '2007-9-5 10:24:11 ' union all
select 121,24,150, '2007-8-27 14:13:38 ' union all
select 122,25,90, '2007-8-27 14:13:48 ' union all
select 136,26,40, '2007-8-27 14:22:34 ' union all
select 138,27,490, '2007-8-27 14:23:39 ' union all
select 140,28,490, '2007-8-27 14:26:26 ' union all
select 175,24,400, '2007-8-27 15:03:04 ' union all
select 189,28,420, '2007-8-27 15:23:58 ' union all
select 226,29,400, '2007-8-27 16:49:31 ' union all
select 230,29,400, '2007-8-28 16:49:31 ' union all
select 245,30,400, '2007-8-27 16:49:31 ' union all
select 246,31,400, '2007-8-27 12:49:31 ' union all
select 247,32,400, '2007-8-27 16:49:31 ' union all
select 248,33,400, '2007-8-27 16:49:31 ' union all
select 398,34,11, '2007-8-27 16:55:31 ' union all
select 318,28,590, '2007-9-7 10:44:01 ' union all
select 386,28,780, '2007-9-7 12:20:29 '
--这个就需要变换一下啦，加个临时表，我用表变量代替了：
declare @TMP table(logId int,userId int,userResult int,logInsertDate varchar(8))
insert @TMP
select null, userId, userResult=max(userResult), logInsertDate=convert(varchar(8),logInsertDate,112) from @T_LOG group by userId, convert(varchar(8),logInsertDate,112)
update a set a.logId = b.logId from @TMP a, @T_Log b where a.userId=b.userId and a.logInsertDate=convert(varchar(8),b.logInsertDate,112) and a.userResult=b.userResult
--select top 10 * from @TMP order by userResult desc
select * from @T_LOG a where logId in (select top 10 logId from @TMP where datediff(day,logInsertDate,a.logInsertDate)=0 order by userResult desc)
order by cast(logInsertDate as int), userResult desc
/*
logIduserIduserResultlogInsertDate
138274902007-08-27 14:23:39.000
140284902007-08-27 14:26:26.000
175244002007-08-27 15:03:04.000
226294002007-08-27 16:49:31.000
245304002007-08-27 16:49:31.000
246314002007-08-27 12:49:31.000
247324002007-08-27 16:49:31.000
248334002007-08-27 16:49:31.000
12225902007-08-27 14:13:48.000
13626402007-08-27 14:22:34.000
230294002007-08-28 16:49:31.000
240283202007-09-05 09:20:28.000
254351702007-09-05 10:24:11.000
386287802007-09-07 12:20:29.000
*/
[解决办法]
create table tb(logId int,userId int,userResult int,logInsertDate datetime)
insert into tb values(240,28,320, '2007-9-5 9:20:28 ')
insert into tb values(254,35,170, '2007-9-5 10:24:11 ')
insert into tb values(121,24,150, '2007-8-27 14:13:38 ')
insert into tb values(122,25,90 , '2007-8-27 14:13:48 ')
insert into tb values(136,26,40 , '2007-8-27 14:22:34 ')
insert into tb values(138,27,490, '2007-8-27 14:23:39 ')
insert into tb values(140,28,490, '2007-8-27 14:26:26 ')
insert into tb values(175,24,400, '2007-8-27 15:03:04 ')
insert into tb values(189,28,420, '2007-8-27 15:23:58 ')
insert into tb values(226,29,400, '2007-8-27 16:49:31 ')
insert into tb values(230,29,400, '2007-8-28 16:49:31 ')
insert into tb values(245,30,400, '2007-8-27 16:49:31 ')
insert into tb values(246,31,400, '2007-8-27 12:49:31 ')
insert into tb values(247,32,400, '2007-8-27 16:49:31 ')
insert into tb values(248,33,400, '2007-8-27 16:49:31 ')
insert into tb values(398,34,11 , '2007-8-27 16:55:31 ')
insert into tb values(318,28,590, '2007-9-7 10:44:01 ')
insert into tb values(386,28,780, '2007-9-7 12:20:29 ')
go
select a.* into temp from tb a,
(select userid,convert(varchar(10),logInsertDate,120) logInsertDate,max(userResult) userResult from tb group by userid,convert(varchar(10),logInsertDate,120)) b
where a.userid = b.userid and convert(varchar(10),a.logInsertDate,120) = convert(varchar(10),b.logInsertDate,120) and a.userResult = b.userResult
select t.* from temp as t
where (select count(*) from temp where convert(varchar(10),logInsertDate,120) = convert(varchar(10),t.logInsertDate,120) and userResult > t.userResult) < 10
order by convert(varchar(10),logInsertDate,120),userresult desc
drop table tb,temp
/*
logId userId userResult logInsertDate
----------- ----------- ----------- -----------------------
138 27 490 2007-08-27 14:23:39.000
140 28 490 2007-08-27 14:26:26.000
175 24 400 2007-08-27 15:03:04.000
226 29 400 2007-08-27 16:49:31.000
245 30 400 2007-08-27 16:49:31.000
246 31 400 2007-08-27 12:49:31.000
247 32 400 2007-08-27 16:49:31.000
248 33 400 2007-08-27 16:49:31.000
122 25 90 2007-08-27 14:13:48.000
136 26 40 2007-08-27 14:22:34.000
230 29 400 2007-08-28 16:49:31.000
240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000
386 28 780 2007-09-07 12:20:29.000
（所影响的行数为 14 行）
*/
[解决办法]
--不要临时表的SQL.
create table tb(logId int,userId int,userResult int,logInsertDate datetime)
insert into tb values(240,28,320, '2007-9-5 9:20:28 ')
insert into tb values(254,35,170, '2007-9-5 10:24:11 ')
insert into tb values(121,24,150, '2007-8-27 14:13:38 ')
insert into tb values(122,25,90 , '2007-8-27 14:13:48 ')
insert into tb values(136,26,40 , '2007-8-27 14:22:34 ')
insert into tb values(138,27,490, '2007-8-27 14:23:39 ')
insert into tb values(140,28,490, '2007-8-27 14:26:26 ')
insert into tb values(175,24,400, '2007-8-27 15:03:04 ')
insert into tb values(189,28,420, '2007-8-27 15:23:58 ')
insert into tb values(226,29,400, '2007-8-27 16:49:31 ')
insert into tb values(230,29,400, '2007-8-28 16:49:31 ')
insert into tb values(245,30,400, '2007-8-27 16:49:31 ')
insert into tb values(246,31,400, '2007-8-27 12:49:31 ')
insert into tb values(247,32,400, '2007-8-27 16:49:31 ')
insert into tb values(248,33,400, '2007-8-27 16:49:31 ')
insert into tb values(398,34,11 , '2007-8-27 16:55:31 ')
insert into tb values(318,28,590, '2007-9-7 10:44:01 ')
insert into tb values(386,28,780, '2007-9-7 12:20:29 ')
go
select t.* from
(
select a.* from tb a,
(select userid,convert(varchar(10),logInsertDate,120) logInsertDate,max(userResult) userResult from tb group by userid,convert(varchar(10),logInsertDate,120)) b
where a.userid = b.userid and convert(varchar(10),a.logInsertDate,120) = convert(varchar(10),b.logInsertDate,120) and a.userResult = b.userResult
) t
where
(
select count(*) from
(
select a.* from tb a,
(select userid,convert(varchar(10),logInsertDate,120) logInsertDate,max(userResult) userResult from tb group by userid,convert(varchar(10),logInsertDate,120)) b
where a.userid = b.userid and convert(varchar(10),a.logInsertDate,120) = convert(varchar(10),b.logInsertDate,120) and a.userResult = b.userResult
) m
where convert(varchar(10),logInsertDate,120) = convert(varchar(10),t.logInsertDate,120
) and userResult > t.userResult) < 10
order by convert(varchar(10),logInsertDate,120),userresult desc
drop table tb
/*
logId userId userResult logInsertDate
----------- ----------- ----------- -----------------------
138 27 490 2007-08-27 14:23:39.000
140 28 490 2007-08-27 14:26:26.000
175 24 400 2007-08-27 15:03:04.000
226 29 400 2007-08-27 16:49:31.000
245 30 400 2007-08-27 16:49:31.000
246 31 400 2007-08-27 12:49:31.000
247 32 400 2007-08-27 16:49:31.000
248 33 400 2007-08-27 16:49:31.000
122 25 90 2007-08-27 14:13:48.000
136 26 40 2007-08-27 14:22:34.000
230 29 400 2007-08-28 16:49:31.000
240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000
386 28 780 2007-09-07 12:20:29.000
（所影响的行数为 14 行）
*/
[解决办法]
/*
Limpire：用不用临时表都一样。用临时表代码清晰点，不用就把SQL写长一点，没实质区别。
*/
--原始数据：@T_LOG
declare @T_LOG table(logId int,userId int,userResult int,logInsertDate datetime)
insert @T_LOG
select 240,28,320, '2007-9-5 9:20:28 ' union all
select 254,35,170, '2007-9-5 10:24:11 ' union all
select 121,24,150, '2007-8-27 14:13:38 ' union all
select 122,25,90, '2007-8-27 14:13:48 ' union all
select 136,26,40, '2007-8-27 14:22:34 ' union all
select 138,27,490, '2007-8-27 14:23:39 ' union all
select 140,28,490, '2007-8-27 14:26:26 ' union all
select 175,24,400, '2007-8-27 15:03:04 ' union all
select 189,28,420, '2007-8-27 15:23:58 ' union all
select 226,29,400, '2007-8-27 16:49:31 ' union all
select 230,29,400, '2007-8-28 16:49:31 ' union all
select 245,30,400, '2007-8-27 16:49:31 ' union all
select 246,31,400, '2007-8-27 12:49:31 ' union all
select 247,32,400, '2007-8-27 16:49:31 ' union all
select 248,33,400, '2007-8-27 16:49:31 ' union all
select 398,34,11, '2007-8-27 16:55:31 ' union all
select 318,28,590, '2007-9-7 10:44:01 ' union all
select 386,28,780, '2007-9-7 12:20:29 '
select * from @T_LOG a where logId in
(/*这里@T_LOG join (子查询)，就相当于前面临时表@TMP的功能*/
select top 10 logId from @T_LOG b join
(select userId, userResult=max(userResult), logInsertDate=convert(varchar(8),logInsertDate,112) from @T_LOG group by userId, convert(varchar(8),logInsertDate,112)) c
on b.userId=c.userId and b.userResult=c.userResult and datediff(day,b.logInsertDate,c.logInsertDate)=0 where datediff(day,a.logInsertDate,b.logInsertDate)=0 order by c.userResult desc)
order by cast(logInsertDate as int), userResult desc
/*
logIduserIduserResultlogInsertDate
138274902007-08-27 14:23:39.000
140284902007-08-27 14:26:26.000
175244002007-08-27 15:03:04.000
226294002007-08-27 16:49:31.000
245304002007-08-27 16:49:31.000
246314002007-08-27 12:49:31.000
247324002007-08-27 16:49:31.000
248334002007-08-27 16:49:31.000
12225902007-08-27 14:13:48.000
13626402007-08-27 14:22:34.000
230294002007-08-28 16:49:31.000
240283202007-09-05 09:20:28.000
254351702007-09-05 10:24:11.000
386287802007-09-07 12:20:29.000
*/