一道有趣的sql题

SELECT field1  , concat(max(case when field2  =1 then field2  end),',' ,max(case when field2  =2 then field2  end),',' ,max(case when field2  =3 then field2  end) ) as 'all' FROM demo GROUP BY field1

搞得我一中午没睡觉，不用group_concat也实现出来了，但是比较麻烦， 需要用户自定义变量来模拟rownumber.

环境：
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));


语句：

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