表結(jié)構(gòu)
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 '創(chuàng)建人'
)
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 分組, 組內(nèi)按 id 排序, 組內(nèi)排序的序號(行號)作為列 idx
image.png
mysql5.7
ROW_NUMBER() 在 mysql8 才開始支持, 對于msqyl5.7或?qū)?yīng)版本的MariaDB,相同功能的實現(xiàn)可以參考如下 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語句表示, 如果當(dāng)前行的 category 與變量 @category相同, 那么, 變量@rn加1,否則,@rn設(shè)置為1
-
@category:=category as category必須在(IF(@category = category, @rn + 1, 1))之后, 否則,判斷時 變量@category的取值是不對的,結(jié)果也就不對了
image.png
可以看到,兩個結(jié)果,在分組變化的地方,idx開始了重新編號,且結(jié)果與 ROW_NUMBER() 一致.
