SQL中row_numer、rank、dense_rank的区别与用途

Score表：

Id	Score
1	100
2	200
3	200
4	300
5	400

row_number，排序后增加行号

SELECT Score, row_nubmer() over(ORDER BY Score DESC) AS Idx FROM Score

结果如下：

Score	Idx
400	1
300	2
200	3
200	4
100	5

加group by可去重排序，外面套个where可以实现SELECT第几位

SELECT Score, row_numer() over(ORDER BY Score DESC) AS Idx FROM Score GROUP BY Score

结果如下：

Score	Idx
400	1
300	2
200	3
100	4

rank，排序序号会中断

SELECT Score, rank() over(ORDER BY Score DESC) AS Idx FROM Score GROUP BY Score

Score	Idx
400	1
300	2
200	3
200	3
100	5

dense_rank，排序序号不会中断

SELECT Score, dense_rank() over(ORDER BY Score DESC) AS Idx FROM Score GROUP BY Score

Score	Idx
400	1
300	2
200	3
200	3
100	4