好用的排名函数~ROW_NUMBER(),RANK(),DENSE_RANK() 三兄弟

排名函数三兄弟,一看名字就知道,都是为了排名而生!但是各自有各自的特色!以下一个例子说明问题!(以下栗子没有使用Partition By 的关键字,整个结果集进行排序)

RANK 每个值一个排名,同样的值排同样的位置,如第一名有2个,下一个值就要排第三,如此类推,表现如下面的 RandNr 列
DENSE_RANK 每个值一个排名,跟Rank 不一致的地方在于它不跳号,会1,2,3 那样排下来
ROW_NUMBER 每行一个排序值,遇到相同的排序条件的时候,按照顺序给值,对应表现如下面RowNr 列

;WITH CTE1(ID,Col1) AS
(
    SELECT 1 ,'AA'
    UNION ALL
    SELECT 1 ,'AA'
    UNION ALL
    SELECT 2 ,'BB'
    UNION ALL
    SELECT 3 ,'CC'
    UNION ALL
    SELECT 3 ,'CC'
    UNION ALL
    SELECT 4 ,'DD'
    UNION ALL
    SELECT 5 ,'EE'
)
SELECT RANK() OVER (ORDER BY ID) AS RankNr,
        DENSE_RANK() OVER (ORDER BY ID) AS DenseNr,
        ROW_NUMBER() OVER (ORDER BY ID) AS RowNr,
        *
    FROM CTE1


RankNr               DenseNr              RowNr                ID          Col1
-------------------- -------------------- -------------------- ----------- ----
1                    1                    1                    1           AA
1                    1                    2                    1           AA
3                    2                    3                    2           BB
4                    3                    4                    3           CC
4                    3                    5                    3           CC
6                    4                    6                    4           DD
7                    5                    7                    5           EE

 

然后其实说起排名函数,over 子句的作用也是相当关键的。

Over 子句后面的内容基本如下  

Over(

Partition By AAA,BBB --表示按照AAA,BBB进行分组,每个分组从1开始计数,如果忽略 Partition By 关键字,就当整个结果集作为一个分组来排序

Order by CCC asc,DDD desc --表示按照ccc,ddd 的执行排序赋予排序值,如果没有特定的排序顺序怎么办呢? 可以使用  (select 1) 或者用 newid() 这个就用于随机排序用的

)

 

三兄弟讲完了~扯一下其它方面的

然而Over 子句还有一个更有用的用法,当使用窗口聚合函数(不是排序函数了) 的时候。Over 子句除了可以指定分组之外(这个貌似是2012之后的版本才支持,2012之前的版本只支持结果集的全部聚合),

比方说我还是拿回之前生成了500行数据的测试表(数据没有贴完整了),有时候做对比和统计还是相当有用的哟~~~

SELECT ID,
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Nr1,    --从首行累加到当前行
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Nr2,            --前一行和当前行求和
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN CURRENT ROW AND  1 FOLLOWING ) AS Nr3        --后一行和当前行求和
    FROM dbo.Tmp123


ID          Nr1         Nr2         Nr3
----------- ----------- ----------- -----------
1           1           1           3
2           3           3           5
3           6           5           7
4           10          7           9
5           15          9           11
6           21          11          13
7           28          13          15
8           36          15          17
9           45          17          19
10          55          19          21
11          66          21          23
12          78          23          25
13          91          25          27
14          105         27          29

 

posted on 2016-02-05 15:33  神崎橙  阅读(851)  评论(0编辑  收藏  举报

导航