select * from tb group by 试卷id,成绩,学号,排名 order by 成绩 desc
partition by 试卷ID order by 成绩 对不同试卷进行成绩排名
select * from tb group by 试卷id,成绩,学号,排名 order by 试卷id,成绩 desc
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
上面反了 SELECT *,[排名]=RANK() OVER(PARTITION BY [试卷ID] ORDER BY 成绩 DESC) FROM @table
partition by 试卷ID order by 成绩 对不同试卷进行成绩排名
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
SELECT *,[排名]=RANK() OVER(PARTITION BY [试卷ID] ORDER BY 成绩 DESC) FROM @table