整表数据:
sql:SELECT s.`name`,s.`year`,MAX(CASE s.cource WHEN '语文' THEN s.score ELSE 0 end) 语文,MAX(CASE s.cource WHEN '数学' THEN s.score ELSE 0 end) 数学 from score s GROUP BY s.`name`,s.`year`
sql:SELECT s.`name`,
substring_index(SUBSTRING_INDEX(GROUP_CONCAT('year:',s.`year`,' score:',s.score), ',', 1) ,',',-1) 一,
substring_index(SUBSTRING_INDEX(GROUP_CONCAT('year:',s.`year`,' score:',s.score), ',', 2) ,',',-1) 二,
substring_index(SUBSTRING_INDEX(GROUP_CONCAT('year:',s.`year`,' score:',s.score), ',', 3) ,',',-1) 三,
substring_index(SUBSTRING_INDEX(GROUP_CONCAT('year:',s.`year`,' score:',s.score), ',', 4) ,',',-1) 四
FROM `score` s GROUP BY s.`name`;
可以继续拆分