56,679
社区成员
发帖
与我相关
我的任务
分享
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
-> GROUP BY c1,c2
-> WITH ROLLUP
-> HAVING c1 IS NOT NULL
-> ) AS A
-> GROUP BY c1
-> WITH ROLLUP;
+-------+------+------+------+------+-------+
| total | B1 | B2 | B3 | B4 | total |
+-------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+-------+------+------+------+------+-------+
5 rows in set, 1 warning (0.00 sec)
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 | B1 | B2 | B3 | B4 | TOTAL |
+-------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| TOTAL | 22 | 24 | 23 | 40 | 109 |
+-------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
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 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
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 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>