mysql 中sum (if())与case

先来一个简单的sum

select sum(qty) as total_qty from inventory_product group by product_id

这样就会统计出所有product的qty.

但是很不幸，我们的系统里面居然有qty为负值。而我只想统计那些正值的qty，加上if function就可以了。 SQL为：

select sum(if(qty > 0, qty, 0)) as total_qty from inventory_product group by product_id

意思是如果qty > 0, 将qty的值累加到total_qty, 否则将0累加到total_qty.

以下是sum(if())的例子：

1
2
3
4
5
select
sum( if( qty > 0, qty, 0)) as total_qty ,
sum( if( qty < 0, 1, 0 )) as negative_qty_count
from inventory_product
group by product_id

上面主要介绍sum (if())的定义，以下是关于数据库行列转换的一些方法整理，也是拷贝其他网友的博客的，做了一些删减。

现整理解法如下：

数据样本：

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

insert into tx values
(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);

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 (0.00 sec)

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(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
-> 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)

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

3. 利用SUM(IF()) 生成列,直接生成结果不再利用子查询
   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)

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

5.使用case，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT ifnull(c1,'total')`,
MAX(
CASE
WHEN c2= 'B1' THEN C3
END
) AS B1,
MAX(
CASE
WHEN c2= 'B2' THEN C3
END
) AS B2,
MAX(
CASE
WHEN c2= 'B3' THEN C3
END
) AS B3,
MAX(
CASE
WHEN c2= 'B4' THEN C3
END
) AS B4
FROM tx
GROUP BY c1
　　

其实数据库中也可以用 CASE WHEN / DECODE 代替 IF