MySQL分组添加序号
19 May 2018接下来讲一个分组添加序号的问题，场景需求是依据每个文件名分组标识序号，标识以后可以根据序号筛选出每个文件组的前几行，具体形式如下：
上述表格转化为：
想要分组添加序号，需要利用MySQL中@变量功能，具体语句如下：
select * from
(select IF(@file=filename,@rank:=@rank+1,@rank:=1) as topic_rank,
@file:=filename as filename,
a.topic
from transposition a) b;
-- 注意：@file变量需要先赋值
transposition数据表创建及导入代码：
-- 创建数据表
create table transposition(
filename varchar(100),
topic varchar(100)
);
-- 往表中插入数据
insert into transposition values("0849053.txt","临时调额-操作-卡片");
insert into transposition values("0849053.txt","卡片-状态-正常");
insert into transposition values("0849053.txt","卡片-属性-固定额度");
insert into transposition values("0849053.txt","卡片-包含-分期");
insert into transposition values("0849053.txt","卡片-包含-分期");
insert into transposition values("0907432.txt","还款金额-依赖-账单");
insert into transposition values("0907432.txt","账单-条件-正常还款");
insert into transposition values("0907432.txt","账单-条件-全额还款");
insert into transposition values("0907432.txt","账单-条件-全额还款");
insert into transposition values("0907432.txt","账单-依赖-还款");
insert into transposition values("0907432.txt","核实-操作-信息");
insert into transposition values("0907432.txt","卡片-属性-账单分期");
insert into transposition values("0909268.txt","调整-操作-额度");
insert into transposition values("0909268.txt","释放-操作-额度");
insert into transposition values("0909268.txt","额度-依赖-还款");
insert into transposition values("0909268.txt","核实-操作-身份");
insert into transposition values("0909268.txt","账单-依赖-还款");
insert into transposition values("0909268.txt","账单-依赖-还款");
分类: 数据库技术