t_score(分数表)
t_student(学生表)
执行Sql(效果如下):
SELECT s.`name`,
(CASE o.`subject` WHEN '语文' THEN o.score ELSE NULL END)AS Chinese,
(CASE o.`subject` WHEN '英语' THEN o.score ELSE NULL END)AS English,
(CASE o.`subject` WHEN '数学' THEN o.score ELSE NULL END)AS math,
(CASE o.`subject` WHEN '地理' THEN o.score ELSE NULL END)AS geography,
(CASE o.`subject` WHEN '历史' THEN o.score ELSE NULL END)AS history
FROM `t_student` s LEFT JOIN `t_score` o ON s.`student_id`=o.`student_id
嵌表
SELECT a.name,COALESCE(SUM(Chinese),-1) Chinese,IFNULL(SUM(English),-1) English,IFNULL(SUM(math),-1) math,COALESCE (SUM(geography),-1) geography,IFNULL(SUM(history),-1) history FROM
(SELECT s.`name`,
(CASE o.`subject` WHEN '语文' THEN o.score ELSE NULL END)AS Chinese,
(CASE o.`subject` WHEN '英语' THEN o.score ELSE NULL END)AS English,
(CASE o.`subject` WHEN '数学' THEN o.score ELSE NULL END)AS math,
(CASE o.`subject` WHEN '地理' THEN o.score ELSE NULL END)AS geography,
(CASE o.`subject` WHEN '历史' THEN o.score ELSE NULL END)AS history
FROM `t_student` s LEFT JOIN `t_score` o ON s.`student_id`=o.`student_id` )
AS a
GROUP BY `name`