mysql列转行和行转列

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

参考链接：https://leetcode.cn/problems/rearrange-products-table/solution/zong-jie-xing-zhuan-lie-lie-zhuan-xing-b-g2je/

---

一、表数据(上表/行表：student1；下表/列表：student2)？

二、行列转换

1.行转列

行转列——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

2.列转行

列转行——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

总结

因为使用了group by分组语句，所以需要使用max/sum聚合函数