SQL-ROWNUMBER-OVER汇总

目标：分组并读分组内的数据进行编号排序

实现方案

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 ...) 函数的分组排序编号效果，可能会影响查询的性能和稳定性，应该谨慎使用