MySQL的行列转换

MySQL的行列转换的写法, 如果有需要可以参考下

现在如下有两个表:

成绩表(t_score)

id subjectid score 1 1 95 2 2 85 3 3 90 4 4 88 5 5 98 6 6 89 7 7 90 8 8 88 9 9 87

科目表(t_subject)

id subject 1 语文 2 数学 3 英语 4 物理 5 化学 6 生物 7 政治 8 历史 9 地理

想要转成如下的表:

id 语文 数学 英语 物理 化学 生物 政治 历史 地理 1 95 85 90 88 98 89 90 88 87

---

中间代码

select     CONCAT("  sum(case when j.`subject`='",`subject`,"' then s.score end) as '",`subject`,"',") as tempcodefrom t_subject

查询结果

tempcode sum(case when j.subject=’语文’ then s.score end) as ‘语文’, sum(case when j.subject=’数学’ then s.score end) as ‘数学’, sum(case when j.subject=’英语’ then s.score end) as ‘英语’, sum(case when j.subject=’物理’ then s.score end) as ‘物理’, sum(case when j.subject=’化学’ then s.score end) as ‘化学’, sum(case when j.subject=’生物’ then s.score end) as ‘生物’, sum(case when j.subject=’政治’ then s.score end) as ‘政治’, sum(case when j.subject=’历史’ then s.score end) as ‘历史’, sum(case when j.subject=’地理’ then s.score end) as ‘地理’,

---

最终转换代码

select s.`id`,  sum(case when j.`subject`='语文' then s.score end) as '语文',  sum(case when j.`subject`='数学' then s.score end) as '数学',  sum(case when j.`subject`='英语' then s.score end) as '英语',  sum(case when j.`subject`='物理' then s.score end) as '物理',  sum(case when j.`subject`='化学' then s.score end) as '化学',  sum(case when j.`subject`='生物' then s.score end) as '生物',  sum(case when j.`subject`='政治' then s.score end) as '政治',  sum(case when j.`subject`='历史' then s.score end) as '历史',  sum(case when j.`subject`='地理' then s.score end) as '地理'from t_score s inner join t_subject j on s.subjectid=j.`id`

查询结果就是上面的结果, 就不再贴出了.