MySQL：行列转换（行转列、列转行）

MySQL：行列转换

• 行表 row：

  name	item	score
  小明	math	0
  小明	physics	99
  小亮	math	100
  小亮	physics	0

• 列表 col：

  name	math	physics
  小明	0	99
  小亮	100	0

行转列

1. 方式一：GROUP BY + CASE WHEN

   SELECT name,
   	MAX( CASE item WHEN 'math' THEN score ELSE 0 END ) AS 'math',
   	MAX( CASE item WHEN 'physics' THEN score ELSE 0 END ) AS 'physics' 
   FROM row
   GROUP BY name
   

2. 方式二：GROUP BY + IF

   SELECT name,
       SUM( IF( item = 'math', score, 0 )) AS 'math',
   	SUM( IF( item = 'physics', score, 0 )) AS 'physics'
   FROM row 
   GROUP BY name
   

列转行

1. 方式一：GROUP BY + UNION

   SELECT
       name, 'math' , math AS score
   FROM col
   GROUP BY name
   UNION
   SELECT
       name, 'physics' , physics AS score
   FROM col
   GROUP BY name
   

2. 方式二：DISTINCT + UNION

   SELECT
       distinct name, 'math' , math AS score
   FROM col
   UNION
   SELECT
       distinct name, 'physics' , physics AS score
   FROM col