求一条比较复杂的sql语句

[oFengKun]

简化后有这样一张表（人员成绩表 ）
表结构如下： id unitId subjectId score
1 a 1001 80
2 b 1002 90
3 b 1001 90
4 c 1003 70
.........

想查询后得出这样的记录
subjectId a b c d e .....
1001 sum(score) sum(score) sum(score) sum(score) sum(score)
1002 sum(score) sum(score) sum(score) sum(score) sum(score)
1003 sum(score) sum(score) sum(score) sum(score) sum(score)
1004 sum(score) sum(score) sum(score) sum(score) sum(score)

[oFengKun]

引用 1 楼 roy_88 的回复:

try

select group_concat('sum(if(unitId=''',unitId,''',score,0)) as "',unitId,'"') into  @sql FROM (SELECT DISTINCT unitId 人员成绩表) AS t;
set @sql=concat('select subjectId,',@sql,' from 人员成绩表 group by subjectId ');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

如果不用存储过程，能写出来吗

[中国风]

这是动态写法 你的unitId为动态，不是固定值 固定值可以这样

select subjectId
,SUM(IF(unitId='a',score,0)) AS a 
,SUM(IF(unitId='b',score,0)) AS b
#,--其它同上
FROM  人员成绩表 
group by subjectId

[中国风]

try

select group_concat('sum(if(unitId=''',unitId,''',score,0)) as "',unitId,'"') into  @sql FROM (SELECT DISTINCT unitId 人员成绩表) AS t;
set @sql=concat('select subjectId,',@sql,' from 人员成绩表 group by subjectId ');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;