统计排名的问题

Question

学生 班级 分数
学生A A班 100
学生B B班 90
学生C C班 80
学生D A班 70
学生E B班 60
学生F C班 50
学生G A班 40
学生H B班 30
学生I C班 20
学生J C班 10
查询结果是
学生 班级 分数 班级内排名 全校排名
学生A A班 100 1 1
学生D A班 70 2 4
学生G A班 40 3 7这个意思就是说在只查询这个班级的学生的时候计算出这个学生在这个班级内的排名,而且还要计算出这个学生在全校的排名,以此类推还有可能计算这个学生在全市的排名等等,如果单单查询这个班级的学生成绩并得到排名这个也许好弄我的方式是SELECT ISNULL(成绩字段,0),
       ROW_NUMBER() OVER(ORDER BY ISNULL(成绩字段,0) DESC)
FROM   学生成绩表但是假如还要计算这个学生在全年校排名或者更多的情况下,如果采用高效的方法得到?

Answer 1

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([学生] nvarchar(3),[班级] nvarchar(2),[分数] int)
Insert #T
select N'学生A',N'A班',100 union all
select N'学生B',N'B班',90 union all
select N'学生C',N'C班',80 union all
select N'学生D',N'A班',70 union all
select N'学生E',N'B班',60 union all
select N'学生F',N'C班',50 union all
select N'学生G',N'A班',40 union all
select N'学生H',N'B班',30 union all
select N'学生I',N'C班',20 union all
select N'学生J',N'C班',10
Go
SELECT *,排名
FROM 
(Select 
*,DENSE_RANK()OVER(ORDER BY [分数] desc) AS 排名,班內=DENSE_RANK()OVER(PARTITION BY [班级] ORDER BY 分数 desc) 
from #T
)T
WHERE [班级]=N'A班' AND 班內<=3

Answer 2

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([学生] nvarchar(3),[班级] nvarchar(2),[分数] int)
Insert #T
select N'学生A',N'A班',100 union all
select N'学生B',N'B班',90 union all
select N'学生C',N'C班',80 union all
select N'学生D',N'A班',70 union all
select N'学生E',N'B班',60 union all
select N'学生F',N'C班',50 union all
select N'学生G',N'A班',40 union all
select N'学生H',N'B班',30 union all
select N'学生I',N'C班',20 union all
select N'学生J',N'C班',10
Go
SELECT 
学生,班级,分数,班级内排名,全校排名
FROM 
(Select 
*,DENSE_RANK()OVER(ORDER BY [分数] desc) AS 全校排名,班级内排名=DENSE_RANK()OVER(PARTITION BY [班级] ORDER BY 分数 desc) 
from #T
)T
WHERE [班级]=N'A班' AND 班级内排名<=3
/*
学生 班级 分数 班级内排名 全校排名
学生A A班 100 1 1
学生D A班 70 2 4
学生G A班 40 3 7
*/