T-SQL筆記63_排名函數(ROW_NUMBER,RANK,DENSE_RANK)_分組排序

 

ROW_NUMBER()函數

ROW_NUMBER()要配合 OVER()一起使用，生成的序號可以是升序的，也可以是降序的。

首先使用 SQL 語句創建一個表，然後添加一些數據，SQL 語句如下

/*
CREATE TABLE Student
(
Id int primary key not null,
SName nvarchar(5),
SAge int
)
INSERT INTO Student(Id,SName,SAge) VALUES(1,'Ted',23),(2,'Sam',18),(3,'Jack',29),(4,'Mike',21)
GO
*/
select * from Student
--用ROW_NUMBER來針對年齡由小到大編列序號
SELECT (ROW_NUMBER() OVER(ORDER BY S.SAge ASC)) AS 序號,Id,SName
AS 姓名,SAge AS 年齡
FROM Student AS S

分組排序

在 ROW_NUMBER()函數的 OVER()中，可以使用 PARTITION BY 關鍵字進行分組排序。

首先是根據第一個欄位分組，分組後再在組內根據第二個欄位再排序並加上序號。

PARTITION BY 關鍵字的基本語法如下：

ROW_NUMBER() OVER(PARTITION BY 欄位1 ORDER BY 欄位2)

將上面的 Student 表中再添加一個城市的字段 City，新建一個Student2的表。

ROW_NUMBER,RANK,DENSE_RANK各自比較

/*
drop table Student2

CREATE TABLE Student2
(
    Id int primary key not null,
    SName nvarchar(50),
    SAge int,
    City nvarchar(10)
);

INSERT INTO Student2(Id, SName, SAge, City) VALUES
(1, 'Ted', 23, '台北'),
(2, 'Sam', 18, '新竹'),
(3, 'Jack', 18, '台中'),
(4, 'Mike', 21, '台北'),
(5, 'Anna', 25, '新竹'),
(6, 'Lucy', 25, '台北'),
(7, 'John', 22, '新竹');

GO
*/
SELECT City AS 城市,SAge AS 年齡, ROW_NUMBER() OVER(PARTITION BY　City ORDER BY SAge) AS 排序
FROM Student2 AS S

SELECT City AS 城市,SAge AS 年齡, RANK() OVER(ORDER BY SAge) AS　RANK序號,ROW_NUMBER() OVER(ORDER BY SAge) AS ROW_NUMBER序號
FROM Student2

SELECT City AS 城市,SAge AS 年龄, DENSE_RANK() OVER(ORDER BY SAge)　AS DENSE_RANK序號,RANK() OVER(ORDER BY SAge) AS RANK序號
FROM Student2

在 Student2 表中使用 PARTITION BY 子句先根據“City”欄位分組，分組後再根據 SAge 排序。

RANK()函數，也是對表中的行記錄進行排序，但是它與ROW_NUMBER()排序的規則不一樣。

會考慮到排序的欄位存在重覆值的情況：

RANK()：如果排序欄位存在相同的值，則會使用一樣的序號表示。

ROW_NUMBER()：如果排序欄位存在相同的值，則繼續使用遞增的序號表示。

前 2 列記錄中，年齡都是 18，此時 RANK()生成的序號是 1、1以表示具有相同的年齡值。

而 ROW_NUMBER()生成的序號是1、2繼續遞增排列。

最後 2 列記錄中，年齡都是 25，此時 RANK()生成的序號是 6、6，以表示具有相同的年齡值。

而 ROW_NUMBER()生成的序號是 6、7，繼續遞增排列。

DENSE_RANK()函數與 RANK()函數在功能上有些類似，唯一的區別就是序號是連續的。

RANK()函數生成的序號是不連續的，在上面的例子中，可看到的前 2 列和後 2 列顯示的序號差別很大，就是因為不連續，中間斷開了。

DENSE_RANK()函數生成的序號會連續顯示，不會中斷。

而 RANK()生成的序號是會中斷的，如第 3 列記錄，顯示成了 3，2 被中斷了。