我前一个帖子是问怎么取一天历史记录的前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
要求,每天的前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
SELECT *
FROM t_LOG t_log
WHERE (userResult =
(SELECT TOP 10 MAX(userResult)
FROM t_LOG
WHERE (userId = t_log.userId) AND datediff(dd, t_log.logInsertDate, logInsertDate)
= 0 ))
ORDER BY userResult DESC, logId可是不对啊,取出来的不是我要的,它把11分的也当前10名了~
*
from
ta a
where
userResult in (select top 10 userResult from ta where convert(varchar(10),logInsertDate,120)=convert(varchar(10),a.logInsertDate,120) order by userResult desc)
2005可用row_number()
select *,ID=identity(int,1,1) into # from ta order by erResult descselect * from # a where (select count(1) from # where convert(varchar(10),logInsertDate,120)=convert(varchar(10),a.logInsertDate,120) and ID>a.ID)<10
要的结果是:
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
=================================================而不是:
logId userId userResult logInsertDate
386 28 780 2007-9-7 12:20:29
318 28 590 2007-9-7 10:44:01 *不应该有这条记录
138 27 490 2007-8-27 14:23:39
140 28 490 2007-8-27 14:26:26
189 28 420 2007-8-27 15:23:58
248 33 400 2007-8-27 16:49:31
247 32 400 2007-8-27 16:49:31
246 31 400 2007-8-27 12:49:31
245 30 400 2007-8-27 16:49:31
226 29 400 2007-8-27 16:49:31
175 24 400 2007-8-27 15:03:04
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
121 24 150 2007-8-27 14:13:38 *不应该有这条记录,少一条27日的记录
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 logId=max(logId), userId, userResult=max(userResult), logInsertDate=convert(varchar(8),logInsertDate,112) from @T_LOG group by userId, convert(varchar(8),logInsertDate,112)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/*
logId userId userResult logInsertDate
138 27 490 2007-08-27 14:23:39.000
189 28 420 2007-08-27 15:23:58.000
226 29 400 2007-08-27 16:49:31.000
175 24 400 2007-08-27 15:03:04.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.000230 29 400 2007-08-28 16:49:31.000240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000386 28 780 2007-09-07 12:20:29.000
*/
insert @ta 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
@ta a
where
(select count(distinct userId) from @ta where convert(varchar(10),logInsertDate,120)=convert(varchar(10),a.logInsertDate,120) and userResult <a.userResult)<10order by logInsertDate desc, userResult desc
(所影响的行数为 18 行)logId userId userResult logInsertDate
----------- ----------- ----------- ------------------------------------------------------
386 28 780 2007-09-07 12:20:29.000
318 28 590 2007-09-07 10:44:01.000
254 35 170 2007-09-05 10:24:11.000
240 28 320 2007-09-05 09:20:28.000
230 29 400 2007-08-28 16:49:31.000
398 34 11 2007-08-27 16:55:31.000
245 30 400 2007-08-27 16:49:31.000
247 32 400 2007-08-27 16:49:31.000
248 33 400 2007-08-27 16:49:31.000
226 29 400 2007-08-27 16:49:31.000
189 28 420 2007-08-27 15:23:58.000
175 24 400 2007-08-27 15:03:04.000
136 26 40 2007-08-27 14:22:34.000
122 25 90 2007-08-27 14:13:48.000
121 24 150 2007-08-27 14:13:38.000
246 31 400 2007-08-27 12:49:31.000(所影响的行数为 16 行)
insert @ta 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
@ta a
where
(select count(distinct userId) from @ta where convert(varchar(10),logInsertDate,120)=convert(varchar(10),a.logInsertDate,120) and userResult >a.userResult)<10--改一下</>order by convert(varchar(10),logInsertDate,120) desc, userResult desc
(所影响的行数为 18 行)logId userId userResult logInsertDate
----------- ----------- ----------- ------------------------------------------------------
386 28 780 2007-09-07 12:20:29.000
318 28 590 2007-09-07 10:44:01.000
240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000
230 29 400 2007-08-28 16:49:31.000
138 27 490 2007-08-27 14:23:39.000
140 28 490 2007-08-27 14:26:26.000
189 28 420 2007-08-27 15:23:58.000
226 29 400 2007-08-27 16:49:31.000
175 24 400 2007-08-27 15:03:04.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
121 24 150 2007-08-27 14:13:38.000
122 25 90 2007-08-27 14:13:48.000
136 26 40 2007-08-27 14:22:34.000(所影响的行数为 17 行)
应该是:
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/*
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.000230 29 400 2007-08-28 16:49:31.000240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000386 28 780 2007-09-07 12:20:29.000
*/
select b.*
from (select top 10 userId as userId,max(userResult) as userResult,max(logInsertdate) as logInsertdate from t_log where dateDiff(dd,logInsertDate,getdate())<1 group by userId order by max(userResult) desc) as a
--a为派生表 ,保存今天前十的userId等信息,加上max(logInsertdate)是可能同一userId当天获得了两个相同的最高分(userResult)并且进了前十
--max(logInsertdate)便获取其最后一次获得最高分的时间
inner join t_log b on a.userId=b.userId and a.userResult=b.userResult and a.logInsertdate=b.logInsertdate
--之后只要执行 execute getTop10就可一获得当天的前十
select b.*
from (select top 10 userId as userId,max(userResult) as userResult,max(logInsertdate) as logInsertdate from t_log where dateDiff(dd,logInsertDate,getdate())<1 group by userId order by max(userResult) desc) as a
--a为派生表 ,保存今天前十的userId等信息,加上max(logInsertdate)是可能同一userId当天获得了两个相同的最高分(userResult)并且进了前十
--max(logInsertdate)便获取其最后一次获得最高分的时间
inner join t_log b on a.userId=b.userId and a.userResult=b.userResult and a.logInsertdate=b.logInsertdate order by b.userResult desc
你写的那个临时表办法,我不知道怎么调用为好~
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.userResultselect 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 行)
*/
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')
goselect 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 行)
*/
明天早上我来找你,你一定要在袄~
as
--创建临时表
if exists (select 1
from sysobjects
where id = object_id('#top10')
and type = 'U')
drop table #temp
create table #temp(userId bigint,userResult int,logInsertdate datetime,logInsertDay char(10)) --将t_log表中的userId,userResult,logInsertdate插入到临时表中
insert into #temp select userId,userResult ,logInsertdate,''
from t_log
update #temp set logInsertDay=convert(char(10),logInsertDate)
--获取每天的前十
select userId,max(userResult),max(logInsertdate) from #temp group by userId,logInsertDay order by max(logInsertDate) desc,max(userResult) desc执行execute getTop10 便可获得每天的前十。
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/*
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.000230 29 400 2007-08-28 16:49:31.000240 28 320 2007-09-05 09:20:28.000
254 35 170 2007-09-05 10:24:11.000386 28 780 2007-09-07 12:20:29.000
*/
领导决定不用每天的前10名了,我都迷糊了~
开始说要有,现在说又没有了,我真迷糊了~
谢谢各位了,我昨天弄了一晚上,看明白了~
谢谢~
dawugui(潇洒老乌龟) ( ) 信誉:100
Limpire(昨夜小楼) ( ) 信誉:100 虽然没用上,但是我学到不少东西,谢谢你们~~~^_^