語法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
簡單的說ROW_NUMBER()從1開始，為每一條分組記錄回傳一個數字，這里的ROW_NUMBER() OVER (ORDER BY CYLH DESC) 是先把xlh列降序，再為降序以后的每條CYLH記錄回傳一個序號，
示例： CYLH ROW_MUM
1900 1
1600 2
1185 3
780 4
分析：ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函式計算的值就表示每組內部排序后的順序編號（組內連續的唯一的)
也可這樣使用：ROW_NUMBER() OVER (ORDER BY COL2)
例子：
建立測驗表，并插入測驗資料
CREATE TABLE TEST_ROW_NUMBER_01( CMZH varchar(10) not null, CYLH varchar(10) null, MJE money null, );
INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES (2106000011,20281997,10.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000010,20281996,10.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000008,20281995,0.00) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000006,20281994,9.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000004,20281993,5.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000001,20281992,10.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000002,20281992,10.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000007,20217280,0.00) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000009,20172458,5.50) INSERT INTO TEST_ROW_NUMBER_01(CMZH,CYLH,MJE) VALUES(2106000005,20121813,0.00)
執行腳本自動生成行號并按CYLH進行排序（滑動查看代碼）
SELECT ROW_NUMBER()OVER(ORDER BY CYLH DESC) AS ROWNUM,* FROM TEST_ROW_NUMBER_01
結果如下：
注意：在使用over等開窗函式時，over里頭的分組及排序的執行晚于“where，group by，order by”的執行，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374450.html
標籤：SQL Server
上一篇：SQL GROUPING 運算子