Mysql行列转换

前一段时间，学习了MYSQL，将一些比较常用的语句记录如下，以备以后查阅。

 

一、创建表
DROP TABLE IF EXISTS tb;

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT);
INSERT INTO tb VALUES('张三','语文',74);
INSERT INTO tb VALUES('张三','数学',83);
INSERT INTO tb VALUES('张三','物理',93);
INSERT INTO tb VALUES('李四','语文',74);
INSERT INTO tb VALUES('李四','数学',84);
INSERT INTO tb VALUES('李四','物理',94);

SELECT * FROM tb

1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用IFNULL将汇总行标题显示为 Total
SELECT
IFNULL(c1,'姓名') AS 姓名,
SUM(IF(c2='语文',c3,0)) AS 语文,
SUM(IF(c2='数学',c3,0)) AS 数学,
SUM(IF(c2='物理',c3,0)) AS 物理,
SUM(IF(c2='total',c3,0)) AS 总分
FROM (
SELECT 姓名 c1,IFNULL(课程,'total') AS c2,SUM(分数) AS c3
FROM tb GROUP BY 姓名,课程
WITH ROLLUP
HAVING 姓名 IS NOT NULL
) AS A
GROUP BY c1
WITH ROLLUP;

2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为Total
SELECT 姓名,
SUM(IF(课程='语文',分数,0)) AS 语文,
SUM(IF(课程='数学',分数,0)) AS 数学,
SUM(IF(课程='物理',分数,0)) AS 物理,
SUM(分数) 总分
FROM tb
GROUP BY 姓名
UNION
SELECT '合计',
SUM(IF(课程='语文',分数,0)) AS 语文,
SUM(IF(课程='数学',分数,0)) AS 数学,
SUM(IF(课程='物理',分数,0)) AS 物理,
SUM(分数) 总分 FROM tb;

3. 利用SUM(IF())生成列,直接生成结果不再利用子查询
SELECT
IFNULL(姓名,'合计') 姓名,
SUM(IF(课程='语文',分数,0)) AS 语文,
SUM(IF(课程='数学',分数,0)) AS 数学,
SUM(IF(课程='物理',分数,0)) AS 物理,
SUM(分数) AS 总分
FROM tb
GROUP BY 姓名 WITH ROLLUP ;

4. 动态，适用于列不确定情况，
 SET @EE='';
 SELECT @EE:=CONCAT(@EE,'SUM(IF(课程=\'',课程,'\'',',分数,0)) AS ',课程,',') FROM(SELECT DISTINCT 课程 FROM tb) A;
 SET @QQ=CONCAT('SELECT ifnull(姓名,\'合计\') 姓名,',@EE,' SUM(分数) AS 总分 FROM tb GROUP BY 姓名 WITH ROLLUP');
 PREPARE stmt2 FROM @QQ;
 EXECUTE stmt2;