统计排名的问题

[ccshigenvwa]

学生	班级	分数
学生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   学生成绩表

但是假如还要计算这个学生在全年校排名或者更多的情况下,如果采用高效的方法得到?

[中国风]

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

[中国风]

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
*/