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() 一致.

参考

• https://www.cnblogs.com/chendongblog/p/12559601.html