34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([学号] int,[日期] Date,[年] NVARCHAR(20),[月] int,[日] int,[成绩] int)
Insert #T
select 1,'2005-1-1','2005',1,1,90 union all
select 1,'2005-1-2','2005',1,2,88 union all
select 1,'2005-1-3','2005',1,3,89 union all
select 1,'2006-1-1','2006',1,1,99 union all
select 1,'2006-1-2','2006',1,2,89 union all
select 1,'2006-1-3','2006',1,3,89 union all
select 1,'2007-1-1','2007',1,1,100 union all
select 1,'2007-1-2','2007',1,2,70 union all
select 1,'2007-1-3','2007',1,3,89 union all
select 2,'2005-1-1','2005',1,1,93 union all
select 2,'2005-1-2','2005',1,2,88 union all
select 2,'2005-1-3','2005',1,3,85 union all
select 2,'2006-1-1','2006',1,1,99 union all
select 2,'2006-1-2','2006',1,2,89 union all
select 2,'2006-1-3','2006',1,3,89 union all
select 2,'2007-1-1','2007',1,1,100 union all
select 2,'2007-1-2','2007',1,2,98 union all
select 2,'2007-1-3','2007',1,3,99 union all
select 3,'2005-1-1','2005',1,1,93 union all
select 3,'2005-1-2','2005',1,2,86 union all
select 3,'2005-1-3','2005',1,3,89 union all
select 3,'2006-1-1','2006',1,1,99 union all
select 3,'2006-1-2','2006',1,2,89 union all
select 3,'2006-1-3','2006',1,3,89 union all
select 3,'2007-1-1','2007',1,1,97 union all
select 3,'2007-1-2','2007',1,2,95 union all
select 3,'2007-1-3','2007',1,3,66
Go
--测试数据结束
SELECT #T.学号,COUNT(1) AS 天数 FROM #T join (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 学号 ORDER BY 成绩 DESC) rn1 FROM (Select *,ROW_NUMBER()OVER(PARTITION BY 学号,年 ORDER BY 成绩 DESC) rn from #T WHERE 年 IN ('2005','2006'))tt WHERE rn<=2
)t
ON t.学号 = #T.学号 AND t.rn1=3 WHERE #T.年='2007' AND #T.成绩>t.成绩
GROUP BY #T.学号