語法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

簡單的說row_number()從1開始，為每一條分組記錄返回一個數(shù)字，這里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再為降序以后的沒條xlh記錄返回一個序號。
示例：
xlh?????????? row_num
1700????????????? 1
1500????????????? 2
1085????????????? 3
710??????????????? 4

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據(jù)COL1分組，在分組內(nèi)部根據(jù) COL2排序，而此函數(shù)計算的值就表示每組內(nèi)部排序后的順序編號（組內(nèi)連續(xù)的唯一的)

實例：

初始化數(shù)據(jù)

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

數(shù)據(jù)顯示為

empid?????? deptid????? salary
----------- ----------- ---------------------------------------
1?????????? 10????????? 5500.00
2?????????? 10????????? 4500.00
3?????????? 20????????? 1900.00
4?????????? 20????????? 4800.00
5?????????? 40????????? 6500.00
6?????????? 40????????? 14500.00
7?????????? 40????????? 44500.00
8?????????? 50????????? 6500.00
9?????????? 50????????? 7500.00

需求：根據(jù)部門分組，顯示每個部門的工資等級

預(yù)期結(jié)果：

empid?????? deptid????? salary????????????????????????????????? rank
----------- ----------- --------------------------------------- --------------------
1?????????? 10????????? 5500.00???????????????????????????????? 1
2?????????? 10????????? 4500.00???????????????????????????????? 2
4?????????? 20????????? 4800.00???????????????????????????????? 1
3?????????? 20????????? 1900.00???????????????????????????????? 2
7?????????? 40????????? 44500.00??????????????????????????????? 1
6?????????? 40????????? 14500.00??????????????????????????????? 2
5?????????? 40????????? 6500.00???????????????????????????????? 3
9?????????? 50????????? 7500.00???????????????????????????????? 1
8?????????? 50????????? 6500.00???????????????????????????????? 2

SQL腳本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

總結(jié)

以上是生活随笔為你收集整理的SQL ROW_NUMBER() OVER函数的基本用法用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。