SQL奇技淫巧(01):給查出的數據排序編個號【row_number() over(order by c)】(mysql,db2,oracle,sqlserver通用)
- 2019 年 10 月 3 日
- 筆記
我們天天都在跟資料庫打交道,寫下的程式碼不計其數,寫下的SQL更是可以繞地球幾圈。這裡收集關於SQL的神奇語法及用法,雖然你可能沒有用過,但這些SQL卻可以在關鍵的時候,派上用場。
我對SQL語句的理解,可以比作一座橋樑,將零散的數據組合起來,拿到我所需要的有效資訊。也以此記錄一下使用心得
一. 語法及基礎用法
注意: row_number()不能單獨使用,需要和over(order by col)一起使用。
語法1:
row_number() over(ORDER BY col)
意思:簡單的說row_number()從1開始,為每一條分組記錄返回一個數字,這裡的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再為降序以後的每條xlh記錄返回一個序號。
語法2:
row_number() over(PARTITION BY col1 ORDER BY col2)
意思:表示根據col1分組,在分組內部根據 col2排序,而此函數計算的值就表示每組內部排序後的順序編號(組內連續的唯一的)
關於Parttion by:
Parttion by關鍵字是SQL中分析性函數的一部分,用於給結果集進行分區。它和聚合函數Group by不同的地方在於它只是將原始數據進行名次排列,能夠返回一個分組中的多條記錄(記錄數不變),而Group by是對原始數據進行聚合統計,一般只有一條反映統計值的結果(每組返回一條)。
Parttion by 後面如果想按多個欄位分組,則以逗號分隔。
row_number() over(PARTITION BY col1,col3,col4 ORDER BY col2)
實例:
(1) 建表造數據:
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);
數據顯示為:
(2) 需求:根據部門分組,顯示每個部門的工資等級
sql:
SELECT *, row_number() over(PARTITION by deptid ORDER BY salary desc) as score FROM employee
預期結果:
二. 真實案例
通過上面基礎語法和使用,我們也只是簡單地知道row_number()可以用來編號排序使用,以及分組內排序兩種情景。通過對其有一個了解之後,你的心中充滿疑問, 如果對於在項目中,我們又可以拿它來做什麼,又可以解決什麼樣的問題。
這個語法也是我最近工作內容的時候,才接觸到的,之前也只是模模糊糊地停留在知道有這個,而在工作中沒實際用過它。
上面介紹的是在單表中的一個操作,那麼對於稍微複雜的多表關聯操作是否有考慮過
下面舉個貼近身邊的實例來學習一下SQL用法:
左連接表中可能有多條數據滿足情況,但只取滿足條件中的第一條,即取max 自關聯的查詢數據
select a.*, b.* from girl g, (select *, row_number() over(ORDER BY like desc) as rn from boy) b where b.rn = 1 and g.like = b.like
上面的意思:girl表和boy表關聯,讓girl找到最喜歡的那個boy;boy表是根據喜好值排序。
這樣就可以用上它了。
三. 總結
row_number()的用途非常廣泛,排序最好用它,它會為查詢出來的每一行記錄生成一個序號,依次排序且不會重複,注意使用row_number函數時必須要用over子句選擇對某一列進行排序才能生成序號。
同時,在使用排名函數的時候需要注意以下三點:
-
1、排名函數必須有 OVER 子句。
-
2、排名函數必須有包含 ORDER BY 的 OVER 子句。
-
3、分組內從1開始排序。
這個方法的多資料庫兼容性也是很好的,如果你要與資料庫打交道,也請收藏好它。