07 | 124.67 |
| 2 | shanghai | wm201408 | 127.45 |
| 3 | beijing | wm201403 | 123.23 |
| 3 | beijing | wm201404 | 110.34 |
| 3 | beijing | wm201405 | 131.33 |
| 3 | beijing | wm201406 | 154.58 |
| 3 | beijing | wm201407 | 154.67 |
| 3 | beijing | wm201408 | 167.45 |
| 4 | hangzhou | wm201404 | 110.34 |
| 4 | hangzhou | wm201405 | 131.33 |
| 4 | hangzhou | wm201406 | 154.58 |
| 4 | hangzhou | wm201407 | 154.67 |
| 5 | hangzhou | wm201405 | 131.33 |
| 5 | hangzhou | wm201406 | 154.58 |
| 5 | hangzhou | wm201407 | 154.67 |
+--------+----------+----------+--------+
25 rows in set (0.00 sec)
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 '