mysql 1对多 转为一行_sql中一对多关系的查询结果的多行转换成一行多列

展开全部

1、创建测试表，

create table test_fee(userid number, feeid number, fee number);

2、插入测试数据

insert into test_fee values(1,1001,80);

insert into test_fee values(1,1002,70);

insert into test_fee values(1,1003,90);

insert into test_fee values(1,1004,60);

insert into test_fee values(2,1001,99);

insert into test_fee values(2,1002,66);

insert into test_fee values(2,1001,55);

3、查询表中所有记录，select t.*, rowid from test_fee t，

4、编写sql，按userid汇总，根据不同32313133353236313431303231363533e4b893e5b19e31333431373862的feeid，进行行转列汇总,

select userid,

sum(case when feeid = 1001 then fee else 0 end) as fee_1001,

sum(case when feeid = 1002 then fee else 0 end) as fee_1002,

sum(case when feeid = 1003 then fee else 0 end) as fee_1003,

sum(case when feeid = 1004 then fee else 0 end) as fee_1004

from test_fee t

group by userid