IFNULL将汇总行标题显示为 Total,不甘平庸被左右

逐梦成诗

现收拾解法如下:

行霜走雪 笔者心依然

多少样板:

临磨难 处绝境

create table tx( id int primary key, c1 char, c3 int );

几两人事更悟透

insert into tx values , , , , , , , , , , , , , , , , , , , ;

独来又独往

mysql> select * from tx;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | A1 | B1 | 9 || 2 | A2 | B1 | 7 || 3 | A3 | B1 | 4 || 4 | A4 | B1 | 2 || 5 | A1 | B2 | 2 || 6 | A2 | B2 | 9 || 7 | A3 | B2 | 8 || 8 | A4 | B2 | 5 || 9 | A1 | B3 | 1 || 10 | A2 | B3 | 8 || 11 | A3 | B3 | 8 || 12 | A4 | B3 | 6 || 13 | A1 | B4 | 8 || 14 | A2 | B4 | 2 || 15 | A3 | B4 | 6 || 16 | A4 | B4 | 9 || 17 | A1 | B4 | 3 || 18 | A2 | B4 | 5 || 19 | A3 | B4 | 2 || 20 | A4 | B4 | 5 |+----+------+------+------+20 rows in set

不甘平庸被左右

mysql>

任 沧海桑田变幻

但愿结果

此心未渝

+------+-----+-----+-----+-----+------+|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 |+------+-----+-----+-----+-----+------+

连天岁月 勇与困难艰辛无动于中

1. 施用SUM 生成列 + WITH ROLLUP 生成汇总行,并接纳IFNULL将汇总行标题展现为 Total

誓 闯出崭新后生可畏番好锦绣

mysql> SELECT -> IFNULL AS total, -> SUM AS B1, -> SUM AS B2, -> SUM AS B3, -> SUM AS B4, -> SUM AS total -> FROM ( -> SELECT c1,IFNULL AS c2,SUM 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

------步 方逗

2. 用到SUM 生成列 + UNION 生成汇总行,并接收 IFNULL将汇总行标题展现为 Totalmysql> select c1, -> sum AS B1, -> sum AS B2, -> sum AS B3, -> sum AS B4,SUM AS TOTAL -> from tx -> group by C1 -> UNION -> SELECT 'TOTAL',sum AS B1, -> sum AS B2, -> sum AS B3, -> sum AS B4,SUM 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

挥志当剑

mysql>

历雨经风 激情飞扬

3. 接收SUM 生成列,直接扭转结果不再利用子查询mysql> select ifnull, -> sum AS B1, -> sum AS B2, -> sum AS B3, -> sum AS B4,SUM AS TOTAL -> from tx -> group by C1 with rollup ;+--------------------+------+------+------+------+-------+| ifnull | 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

笑孤独 傲寂寞

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,',LEFT,' ,SUM AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');Query OK, 0 rows affected

焉能随波趋炎凉

mysql> PREPARE stmt2 FROM @QQ;Query OK, 0 rows affected Statement prepared

纵 跌宕沉浮

mysql> EXECUTE stmt2;+--------------------+------+------+------+------+-------+| ifnull | 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 mysql>

激情不减

以上均由网上朋友 liangCK , wwwwb , WWWWA , dap570 提供, 再次多谢她们的扶助。其实数据库中也得以用 CASE WHEN / DECODE代替 IF

充足时代 敢向中外露锋芒

定 攀爬Infiniti高峰创辉煌

------刃 才藏

本文由新葡萄京娱乐场手机版▎永久官网发布于文学天地,转载请注明出处:IFNULL将汇总行标题显示为 Total,不甘平庸被左右

相关阅读