MySQL的行列转换

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 tempcode
from 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`

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