一.行转列
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
