MYSQL 行转列,列转行的方法汇总

一.行转列

1.准备数据

DROP TABLE IF EXISTS tb

CREATE TABLE tb (`cname` VARCHAR(10),cource VARCHAR(10),score INT);

INSERT INTO tb VALUES('Nick', '语文',74);

INSERT INTO tb VALUES('Nick', '数学',83);

INSERT INTO tb VALUES('Nick', '物理',93);

INSERT INTO tb VALUES('Rik','语文',74);

INSERT INTO tb VALUES('Rik','数学',84); I

INSERT INTO tb VALUES('Rik','物理',94);

2.方法

    方法一: 利用SUM(IF()) 生成列      规则: sum(if(新列的列名='  可能的情况' , 展示的结果,0) as 新列的列名='  可能的N种情况'

SELECT

cname AS "姓名",

SUM(IF(cource="语文", score, 0)) AS "语文",

SUM(IF(cource="数学", score, 0)) AS "数学",

SUM(IF(cource="物理", score, 0)) AS "物理"

FROM

tb

GROUP BY cname

方法二.利用max(CASE ... WHEN ... THEN .. ELSE END) AS "语文"的方式来实现   

规则  MAX(  case 新列的列名   when        可能的情况  then 展示的结果,    else  )

SELECT

cname AS "姓名",

MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文",

MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学",

MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理"

FROM tb

GROUP BY `cname`

方法三.

SELECT

保留的字段+

列名-->转换的字段,

列名-->转换的值

FROM (

SELECT

所有用到的字段

FROM

)AS a

UNPIVOT (

转换值  for  转换字段名 in(

a,b,c

)

) as b