DECLARE @table TABLE([试卷ID] int,[学号] int,[成绩] INT)
INSERT INTO @table
SELECT 1,1,89 UNION ALL
SELECT 1,2,90 UNION ALL
SELECT 1,3,70 UNION ALL
SELECT 1,4,56 UNION ALL
SELECT 1,5,56 UNION ALL
SELECT 1,6,84 UNION ALL
SELECT 2,1,81 UNION ALL
SELECT 2,2,86 UNION ALL
SELECT 2,3,98 UNION ALL
SELECT 2,4,93 UNION ALL
SELECT 2,5,89 UNION ALL
SELECT 2,6,88 
--密集排名
SELECT  *,[排名]=DENSE_RANK() OVER(PARTITION BY [试卷ID] ORDER BY 成绩) FROM  @table
/*试卷ID        学号          成绩          排名
----------- ----------- ----------- --------------------
1           4           56          1
1           5           56          1
1           3           70          2
1           6           84          3
1           1           89          4
1           2           90          5
2           1           81          1
2           2           86          2
2           6           88          3
2           5           89          4
2           4           93          5
2           3           98          6

(12 行受影响)
*/
--排名
SELECT  *,[排名]=RANK() OVER(PARTITION BY [试卷ID] ORDER BY 成绩) FROM  @table