MySQL---行转列

MySQL—行转列一、行转列1、SqlServer和Orcle中可以使用pivot行转列函数快速实现，而MySQL中没有pivot:SELECT *FROM studentPIVOT (SUM(score) FOR subject IN (语文, 数学, 英语))2、MySQL实现行转列SELECT name,MAX(CASEWHEN subject='语文'THEN scoreELSE 0END

Shall潇

36640人浏览 · 2021-03-02 14:02:27

Shall潇 · 2021-03-02 14:02:27 发布

MySQL—行转列

一、行转列

1、SqlServer和Orcle中可以使用pivot行转列函数快速实现，而MySQL中没有

pivot:
在这里插入图片描述

SELECT *
FROM student
PIVOT (
    SUM(score) FOR subject IN (语文, 数学, 英语)
)

2、MySQL实现行转列

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 student
GROUP BY name

二、列转行

unpivot
1、其他转换

SELECT *
FROM student1
UNPIVOT (
    score FOR subject IN ("语文","数学","英语")
)

2、MySQL转换

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