簡化后有這樣一張表（人員成績表 ）
表結構如下： 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)
uj5u.com熱心網友回復：
tryselect 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;
uj5u.com熱心網友回復：
如果不用存盤程序，能寫出來嗎
uj5u.com熱心網友回復：
這是動態寫法你的unitId為動態，不是固定值
固定值可以這樣
select subjectId
,SUM(IF(unitId='a',score,0)) AS a
,SUM(IF(unitId='b',score,0)) AS b
#,--其它同上
FROM 人員成績表
group by subjectId
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116671.html
標籤：MySQL