Mysql中模拟rownum完成行列转换



环境：
create table  ff(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