ROW_NUMBER()在SQL2K5版本中新增,該函數返回結果集分區內行的序列號,每個分區的第一行從 1 開始,連續不間斷,后跟OVER BY子句或者PARTITION BY子句

先構造一張表,放一些數據進行,SQL腳本如下

--DROP TABLE T CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT) INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL) SELECT 'a1','b1','c1',10 union all SELECT 'a1','b1','c2',20 union all SELECT 'a1','b2','c2',30 union all SELECT 'a1','b2','c3',40 union all SELECT 'a1','b2','c3',50 union all ? SELECT 'a2','b3','c3',12 union all SELECT 'a2','b3','c3',22 union all SELECT 'a2','b3','c3',32 ? SELECT * FROM T

執行查詢后的結果:

GRP_A GRP_B GRP_C VAL -------------------- -------------------- -------------------- ----------- a1 b1 c1 10 a1 b1 c2 20 a1 b2 c2 30 a1 b2 c3 40 a1 b2 c3 50 a2 b3 c3 12 a2 b3 c3 22 a2 b3 c3 32 ? (8 行受影響)

ORDER BY子句

如果單獨使用ORDER BY子句,則整個結果集為一個分區,

下邊的SQL語句單使用了ORDER BY子句,先按GRP_A排序,然后根據排序后的結果額外生成一連續自增的NUM列

SELECT *, ROW_NUMBER()OVER(ORDER BY GRP_A) AS NUM FROM T

結果集如下,不算復雜啊:

GRP_A GRP_B GRP_C VAL NUM -------------------- -------------------- -------------------- ----------- -------------------- a1 b1 c1 10 1 a1 b1 c2 20 2 a1 b2 c2 30 3 a1 b2 c3 40 4 a1 b2 c3 50 5 a2 b3 c3 12 6 a2 b3 c3 22 7 a2 b3 c3 32 8 ? (8 行受影響)

跟不加ROW_NUMBER函數的區別就是增加了最后那一列,NUM,其值是遞增的,增量為1

同理可以按照其它字段排序,如VAL,或者GRP_B,GRP_C等,列名不變,列值不變,變的是其它列的順序

這個查詢僅有一個分區,就是整個結果集,整個結果集內有這么一列,自增NUM列,可以用來分頁或者啥的啥的

沒有什么特別之處,ORDER BY 跟普通的ORDER BY 類似,也可以有多個列的排序,如:

SELECT *, ROW_NUMBER()OVER(ORDER BY GRP_A ASC,GRP_B DESC,VAL ASC) AS NUM FROM T

最終影響的還是結果集中除NUM列外的其它數據的排序

?

PARTITION BY XXX ORDER BY YYY子句

使用PARTITION BY子句后,結果集就會按照該字段進行分區,這時候仍然要使用ORDER BY子句,影響的是分區內的排序,然后在每個分區內生成從1開始的自增列:

SELECT *, ROW_NUMBER()OVER(PARTITION BY GRP_A ORDER BY VAL) AS NUM FROM T

這時候結果集發生變化了,必須得發生!

GRP_A GRP_B GRP_C VAL NUM -------------------- -------------------- -------------------- ----------- -------------------- a1 b1 c1 10 1 a1 b1 c2 20 2 a1 b2 c2 30 3 a1 b2 c3 40 4 a1 b2 c3 50 5 a2 b3 c3 12 1 a2 b3 c3 22 2 a2 b3 c3 32 3 ? (8 行受影響)
結果集先是按照GRP_A字段進行了分區:a1分區和a2分區,分別為前五行和后三行,在每個分區內NUM從1遞增,增量為1,排序規則為VAL ASC
嗯,很簡單吧,
應用場景
比如我們有一張表,該表存儲了某件商品的圖片,商品與圖片是一對多的關系,即一件商品有N件圖片,現在要求取圖片最多的那個商品
或者每件商品只取按照添加順序的倒序取前兩張圖片,這時候就可以用ROW_NUMBER()OVER(PARTITION BY 商品編號 ORDER BY 添加時間 DESC) AS NUM來取
隨后在結果集內過濾NUM于2的數據即可!
整體不算復雜,更多的應用場景還是分頁時,當我們的自增主鍵不連續時,要取前20條數據,就可以利用該函數特性生成連續遞增的NUM列,再BETWEEN即可
分頁示例
SELECT * FROM (SELECT *, ROW_NUMBER()OVER(ORDER BY VAL) AS NUM FROM T) AS SOMETABLENAME WHERE NUM BETWEEN 1 AND 5

相對應的結果集

GRP_A GRP_B GRP_C VAL NUM -------------------- -------------------- -------------------- ----------- -------------------- a1 b1 c1 10 1 a2 b3 c3 12 2 a1 b1 c2 20 3 a2 b3 c3 22 4 a1 b2 c2 30 5 ? (5 行受影響) 僅返回了前五行

猜測您可能對下邊的文章感興趣

SQL SERVER 2008 函數大全 - 字符串函數

SQL2008系統統計函數

[MSSQL]GROUPING SETS,ROLLUP,CUBE初體驗

[MSSQL]ROW_NUMBER函數

[MSQL]RANK函數

[MSSQL]NTILE另類分頁有么有?!

[MSQL]也說SQL中顯示星期幾函數

[MSSQL]COALESCE與ISNULL函數

[MSSQL]PIVOT函數

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

如果您喜歡該博客請點擊右下角推薦按鈕,您的推薦是作者創作的動力!

轉載于:https://www.cnblogs.com/kkun/archive/2011/08/12/2136012.html

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的[MSSQL]ROW_NUMBER函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。