34,576
社区成员
发帖
与我相关
我的任务
分享
--RESULT.分数相同的并列名次
date number id score
---------- ----------- ----------- -----------
2007-1-1 1 3 100
2007-1-1 2 2 90
2007-1-1 2 4 90
2007-1-1 4 1 80
2007-5-1 1 2 80
2007-5-1 1 3 80
2007-5-1 3 1 70
2007-5-1 4 4 60
2007-7-1 1 1 90
2007-7-1 2 2 80
2007-7-1 3 4 70
2007-7-1 4 3 50
create table mark(id int,date nvarchar(10),score int)
insert mark
select 1,'2007-1-1',80
union all select 2,'2007-1-1',90
union all select 3,'2007-1-1',100
union all select 4,'2007-1-1',90
union all select 1,'2007-5-1',70
union all select 2,'2007-5-1',80
union all select 3,'2007-5-1',80
union all select 4,'2007-5-1',60
union all select 1,'2007-7-1',90
union all select 3,'2007-7-1',50
union all select 2,'2007-7-1',80
union all select 4,'2007-7-1',70
select a.date,sum(case when a.score > b.score then 1 else 0 end) +1 number, b.id,b.score from mark a join mark b on a.date = b.date
group by b.id,a.date,b.score
order by 1,2,3,4
--result
排名 学号 时间 分数
----------- ----------- ---------- -----------
1 2 每二学期 99
2 1 每二学期 90
3 3 每二学期 86
1 2 每一学期 85
2 1 每一学期 80
3 3 每一学期 70
(所影响的行数为 6 行)
create table T(
学号 int,
时间 nvarchar(10),
分数 int
)
insert T select 1, '每一学期', 80
union all select 1, '每二学期', 90
union all select 2, '每一学期', 85
union all select 2, '每二学期', 99
union all select 3, '每一学期', 70
union all select 3, '每二学期', 86
select 排名=(select count(distinct 分数) from T where 时间=tmp.时间 and 分数>=tmp.分数),* from T as tmp
order by 时间,1