[MySQL]行列转换变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）(三)

统计每个用户的使用总量为：

mysql> SELECT Number,SUM(Wm) 'total_num' FROM flow_tmp GROUP BY Number ORDER BY Number;
+--------+-----------+
| Number | total_num |
+--------+-----------+
|      1 |    836.60 |
|      2 |    788.70 |
|      3 |    841.60 |
|      4 |    550.92 |
|      5 |    440.58 |
+--------+-----------+
5 rows in set (0.00 sec)

mysql>

二，行变列例子演示

1，准备测试数据

USE csdn;
DROP TABLE IF EXISTS csdn.tb;
CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;

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;

+--------------------+--------+--------+--------+-----------+--------------+

| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |

+--------------------+--------+--------+--------+-----------+--------------+

| 张三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |

| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |

| 总成绩平均数 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |

+--------------------+--------+--------+--------+-----------+--------------+

2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total_num

SQL代码块如下：

SELECT cname AS "姓名",
	SUM(IF(cource="语文",score,0)) AS "语文",
	SUM(IF(cource="数学",score,0)) AS "数学",
	SUM(IF(cource="物理",score,0)) AS "物理",
	SUM(score) AS "总成绩",
	ROUND(AVG(score),2) AS "平均成绩"
FROM tb 
GROUP BY cname
UNION ALL
SELECT
	"总成绩平均数",
	ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(
	SELECT "all",cname AS "姓名",
		SUM(IF(cource="语文",score,0)) AS "语文",
		SUM(IF(cource="数学",score,0)) AS "数学",
		SUM(IF(cource="物理",score,0)) AS "物理",
		SUM(score) AS "总成绩",
		AVG(score) AS "平均成绩"
	FROM tb 
	GROUP BY cname
)tb2 
GROUP BY tb2.all;

执行结果正确，如下所示：

+--------------------+--------+--------+--------+-----------+--------------+

| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |

+--------------------+--------+--------+--------+-----------+--------------+

| 张三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |

| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |

| 总成绩平均数 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |

+--------------------+--------+--------+--------+-----------+--------------+

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

SQL代码如下：

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 "物理", 
	SUM(score) AS "总成绩",
	ROUND(AVG(score) ,2) AS "平均成绩"
FROM tb 
GROUP BY `cname`
UNION ALL
SELECT
	"总成绩平均数",
	ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(	SELECT '