Mysql中模拟rownum完成行列转换
环境：create tableff(f1 varcahr(10), f2 int);
insert into ff values ('A', round(rand()*100));
insert into ff values ('A', round(rand()*100));
insert into ff values ('A', round(rand()*100));
需要执行一条SQL语句得到一行记录 'A' ,'98,44,55'
语句：
set @row:=0;
select tmp.f1, concat(max(tmp.a),',',max(tmp.b),',',max(tmp.c)) as 'all'
from (
select
@row:=@row+1,
f1,
case when @row=1 then f2 else 0 end as a,
case when @row=2 then f2 else 0 end as b,
case when @row=3 then f2 else 0 end as c
from ff group by @row
)
as tmp group by tmp.f1;
思路：
这个是典型的行转列，然后再concat连成字符串
step1: 在表上添加rowId
select @row:=@row+1, f1,f2 from ff;
step2: 对rowId进行分组
select @row:=@row+1, f1,f2 from ff group by @row
step3: 添加以后用来合成的目标列
select @row:=@row+1, f1,0 as a, 0 as b, 0 as c from ff group by @row
step4: 对于rowid=1的分组， a列取f2值， b,c都是0；对于rowId=2的分组， a,c列取0， b取f2的值... 这里的case when就是行转列的法宝
select
@row:=@row+1,
f1,
case when @row=1 then f2 else 0 end as a,
case when @row=2 then f2 else 0 end as b,
case when @row=3 then f2 else 0 end as c
from ff group by @row
step5: 最后再取max,并且concat
页:
[1]