【T-SQL系列】新的排序函数
如:ROW_NUMBER、RANK、DENSE_RANK
三个分析函数都是按照col1分组内从1开始排序
ROW_NUMBER() 是没有重复值的排序(即使两天记录相等也是不重复的),可以利用它来实现分页
DENSE_RANK() 是连续排序,两个第二名仍然跟着第三名
RANK() 是跳跃拍学,两个第二名下来就是第四名
示例:
DECLARE @t1 TABLE ( Sequence INT , Name VARCHAR(20) , Score INT ) INSERT INTO @t1 SELECT 1 , '甲' , 7 UNION ALL SELECT 2 , '甲' , 8 UNION ALL SELECT 3 , '甲' , 8 UNION ALL SELECT 4 , '甲' , 8 UNION ALL SELECT 5 , '甲' , 9 UNION ALL SELECT 1 , '乙' , 10 UNION ALL SELECT 2 , '乙' , 6 UNION ALL SELECT 3 , '乙' , 10 UNION ALL SELECT 4 , '乙' , 6 UNION ALL SELECT 5 , '乙' , 8 SELECT Sequence , Name , Score , ROW_NUMBER() OVER ( ORDER BY Sequence, Score ) AS RowNumber1 , ROW_NUMBER() OVER ( ORDER BY Sequence, Score DESC ) AS RowNumber2 , RANK() OVER ( ORDER BY Score ) AS Rnk , DENSE_RANK() OVER ( ORDER BY Score ) AS DenseRnk , NTILE(15) OVER ( ORDER BY Score ) AS Bucket FROM @t1
结果集: