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