mysql 分组排序 over_mysql5.7 mysql8窗口函数分组排序并在组内编号

表结构

create table biz_tag

(

id bigint unsigned auto_increment comment '主键' primary key,

tag_name varchar(128) default '' not null comment '标签',

category varchar(10) default '' not null comment '分类',

create_by varchar(20) default '' not null comment '创建人'

)

mysql8

select id, ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS idx, category, tag_name

from biz_tag

where create_by = 'sys'

;

ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS idx

含义: 按 category 分组, 组内按 id 排序, 组内排序的序号(行号)作为列 idx

image.png

mysql5.7

ROW_NUMBER() 在 mysql8 才开始支持, 对于msqyl5.7或对应版本的MariaDB,相同功能的实现可以参考如下 sql

select id, @rn:= (IF(@category = category, @rn + 1, 1)) as idx, @category:=category as category, tag_name

from (select category,id,tag_name from biz_tag where create_by = 'sys' order by category, id) a

, (select @rn:=0,@category:='') b

;

要点:

a 表的 order by 必须先 category, 后 id

IF语句表示, 如果当前行的 category 与变量 @category相同, 那么, 变量@rn加1,否则,@rn设置为1

@category:=category as category 必须在 (IF(@category = category, @rn + 1, 1))之后, 否则,判断时 变量@category 的取值是不对的,结果也就不对了

image.png

可以看到,两个结果,在分组变化的地方,idx开始了重新编号,且结果与 ROW_NUMBER() 一致.

参考