SQL语句中经典 行转列、列转行问题解答

行转列用case...when或if分类讨论, group by进行分组。

列转行用union或union all将多列的字段整合到一行。

（当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。推荐日常开发中，能使用union all就使用union all）

【行转列——MAX/SUM+CASE WHEN+GROUP BY】

SELECT name,
  MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) AS "语文",
  MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",
  MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
FROM student1
GROUP BY name

【列转行——MAX+UNION+GROUP BY】

SELECT NAME,'语文' AS subject,MAX("语文") AS score
FROM student2 GROUP BY NAME
UNION
SELECT NAME,'数学' AS subject,MAX("数学") AS score
FROM student2 GROUP BY NAME
UNION
SELECT NAME,'英语' AS subject,MAX("英语") AS score
FROM student2 GROUP BY NAME