数据样本:
create table tx(
);
insert into tx(c1,c2,c3) values
('A1','B1',9),
('A2','B1',7),
('A3','B1',4),
('A4','B1',2),
('A1','B2',2),
('A2','B2',9),
('A3','B2',8),
('A4','B2',5),
('A1','B3',1),
('A2','B3',8),
('A3','B3',8),
('A4','B3',6),
('A1','B4',8),
('A2','B4',2),
('A3','B4',6),
('A4','B4',9),
('A1','B4',3),
('A2','B4',5),
('A3','B4',2),
('A4','B4',5);
mysql> select * from tx;
+----+------+------+------+
| id | c1
+----+------+------+------+
|
|
|
|
|
|
|
|
|
| 10 | A2
| 11 | A3
| 12 | A4
| 13 | A1
| 14 | A2
| 15 | A3
| 16 | A4
| 17 | A1
| 18 | A2
| 19 | A3
| 20 | A4
+----+------+------+------+
20 rows in set (0.00 sec)
mysql>
期望结果
+------+-----+-----+-----+-----+------+
|C1
+------+-----+-----+-----+-----+------+
|A1
|A2
|A3
|A4
|Total |22
+------+-----+-----+-----+-----+------+
1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql>
SELECT
IFNULL(c1,'total') AS total,
SUM(IF(c2='B1',c3,0)) AS B1,
SUM(IF(c2='B2',c3,0)) AS B2,
SUM(IF(c2='B3',c3,0)) AS B3,
SUM(IF(c2='B4',c3,0)) AS B4,
SUM(IF(c2='total',c3,0)) AS total
FROM (
SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
FROM tx
WITH ROLLUP
HAVING c1 IS NOT NULL
) AS A
GROUP BY c1
WITH ROLLUP;
+-------+------+------+------+------+-------+
| total | B1
+-------+------+------+------+------+-------+
| A1
| A2
| A3
| A4
| total |
+-------+------+------+------+------+-------+
5 rows in set, 1 warning (0.00 sec)
/*
select c1,c2,sum(c3) from tx group by c1,c2
*/
2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql>
select c1,
sum(if(c2='B1',C3,0)) AS B1,
sum(if(c2='B2',C3,0)) AS B2,
sum(if(c2='B3',C3,0)) AS B3,
sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
from tx
group by C1
UNION
SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
sum(if(c2='B2',C3,0)) AS B2,
sum(if(c2='B3',C3,0)) AS B3,
sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX;
+-------+------+------+------+------+-------+
| c1
+-------+------+------+------+------+-------+
| A1
| A2
| A3
| A4
| TOTAL |
+-------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
3.
mysql>
select
ifnull(c1,'total'),
sum(if(c2='B1',C3,0)) AS B1,
sum(if(c2='B2',C3,0)) AS B2,
sum(if(c2='B3',C3,0)) AS B3,
sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
from tx
group by C1 with rollup ;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1
+--------------------+------+------+------+------+-------+
| A1
| A2
| A3
| A4
| total
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
4. 动态,适用于列不确定情况,
mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1
+--------------------+------+------+------+------+-------+
| A1
| A2
| A3
| A4
| total
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
其实数据库中也可以用 CASE WHEN / DECODE
sum(if(c2='B1',C3,0)) AS B1
可改写为
sum(case c2 when 'B1' then C3 else 0 end) AS B1