目标:分组并读分组内的数据进行编号排序
实现方案
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 ...) 函数的分组排序编号效果,可能会影响查询的性能和稳定性,应该谨慎使用
标签:COMPANY,ROWNUMBER,ORDER,field,num,分组,SQL,OVER,SELECT From: https://www.cnblogs.com/oumi/p/18096339