SQL-ROWNUMBER-OVER彙總

李文学發表於2024-03-26
SQL

目標：分組並讀分組內的資料進行編號排序

實現方案

ROW_NUMBER() OVER()

具體實現

ORACLE

Select b.*,b.Fbqd, b.Qzsm
From 
(
　　Select Row_Number() Over(Partition By Cpdm, Fbqd Order By Ksrq Desc) iRow,
   Cpid, Cpdm,Djxh,Djmc,Fbqd
　　From t_biao
) b

SQLite

SELECT year, product_id, amount,
       ROW_NUMBER() OVER (PARTITION BY year, product_id ORDER BY year,     product_id, amount) AS rownum
FROM sales;

MSSQL

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

MYSQL

示例一：

MYSQL(<5.8)

用法：
SELECT
     IF(@temp=列名1, @rank:=@rank+1,@rank:=1) iRow,
     @temp:=列名1,
      列名2,      ......,     列名n 
FROM 表
ORDER BY 列名1,列名2, 升序或降序;
SELECT 
    IF(@temp=datetime, @rank:=@rank+1, @rank:=1) rn, /*結果集編號*/
    @temp:=datetime, 
    createdate 
FROM jd_byjhplan 
ORDER BY datetime, createdate ASC;/*分組、排序*/

MYSQL(>5.8)

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
    FROM your_table
) AS subquery
WHERE row_num <= 10;

示例二：

單欄位分組

按照公司分組、建立時間排序，並對每一組內的行進行編號

現在這裡有一張使用者表 user，裡面包含以下欄位：ID 主鍵、USERNAME 使用者名稱、PASSWORD 密碼、COMPANY 公司、DEPT 部門、CREATE_TIME 建立時間

MYSQL(8.0)

SELECT
    t.*,
    row_number() over(PARTITION BY t.COMPANY ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
    user t 
WHERE
    t.ID > 0;

MYSQL(5.7)

SELECT
    t.*,
    @num := IF(@field_1 <=> t.COMPANY, @num + 1, 1) AS ROW_NO,
    @field_1 := t.COMPANY AS FIELD_1
FROM
    user t,
    (SELECT @num := 0, @field_1 := NULL) a 
WHERE
    t.ID > 0 
ORDER BY
    t.COMPANY,
    t.CREATE_TIME;

說明：

這個 SQL 語句中使用使用者變數 @num 和 @field_1，分別表示當前行的排序編號和前一個分組的 COMPANY 欄位的值。

在 SELECT 子句中，使用了 IF 函式來判斷當前行是否與前一行屬於同一分組，如果是，將當前行的排序編號加 1，否則，將排序編號重置為 1。

在 ORDER BY 子句中，需要將分組欄位 COMPANY 放在前面，將排序欄位 CREATE_TIME 放在後面。

多欄位分組

按照公司和部門分組、建立時間排序，並對每一組內的行進行編號

MYSQL(8.0)

SELECT
    t.*,
    row_number() over(PARTITION BY t.COMPANY,t.DEPT ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
    user t 
WHERE
    t.ID > 0;

MYSQL(5.7)

SELECT
    t.*,
    @num := IF(@field_1 <=> t.COMPANY && @field_2 <=> t.DEPT, @num + 1, 1) AS ROW_NO,
    @field_1 := t.COMPANY AS FIELD_1,
    @field_2 := t.DEPT AS FIELD_2 
FROM
    user t,
    (SELECT @num := 0, @field_1 := NULL, @field_2 := NULL) a 
WHERE
    t.ID > 0 
ORDER BY
    t.COMPANY,
    t.DEPT,
    t.CREATE_TIME;

說明：

這個 SQL 語句中使用使用者變數 @num 、@field_1 和 @field_2，分別表示當前行的排序編號、前一個分組的 COMPANY 欄位的值和 DEPT 欄位的值。

在 SELECT 子句中，使用了 IF 函式來判斷當前行是否與前一行屬於同一分組，如果是，將當前行的排序編號加 1，否則，將排序編號重置為 1。

在 ORDER BY 子句中，需要將分組欄位 COMPANY 和 DEPT 放在前面，將排序欄位 CREATE_TIME 放在後面。

注意：
使用使用者變數來實現類似 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 函式的分組排序編號效果，可能會影響查詢的效能和穩定性，應該謹慎使用

相關文章