表結(jié)構(gòu)
create table biz_tag
(
id bigint unsigned auto_increment comment '主鍵' primary key,
tag_name varchar(128) default '' not null comment '標(biāo)簽',
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)排序的序號(hào)(行號(hào))作為列 idx
image.png
mysql5.7
ROW_NUMBER() 在 mysql8 才開(kāi)始支持, 對(duì)于msqyl5.7或?qū)?yīng)版本的MariaDB,相同功能的實(shí)現(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
;
要點(diǎn):
- a 表的 order by 必須先 category, 后 id
- IF語(yǔ)句表示, 如果當(dāng)前行的 category 與變量 @category相同, 那么, 變量@rn加1,否則,@rn設(shè)置為1
-
@category:=category as category必須在(IF(@category = category, @rn + 1, 1))之后, 否則,判斷時(shí) 變量@category的取值是不對(duì)的,結(jié)果也就不對(duì)了
image.png
可以看到,兩個(gè)結(jié)果,在分組變化的地方,idx開(kāi)始了重新編號(hào),且結(jié)果與 ROW_NUMBER() 一致.
