在mysql中使用COUNT 或者SUM函数计算记录总数

最新推荐文章于 2024-06-21 14:04:18 发布

weixin_34111790

最新推荐文章于 2024-06-21 14:04:18 发布

阅读量2.4k

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/2331760/blog/1603406

版权

2019独角兽企业重金招聘Python工程师标准>>>

count函数的作用

想要真正的理解count函数，我们就必须明白count函数的作用。
作用一：统计某一列非空(not null)值得数量，即统计某列有值得结果数,使用count(col),其中col为设置为not null 的行
作用二：统计结果集的行数，此时不用管某列是否为null值。即使用count(*).
明白了这点，我们就应该知道MySQL的count(*)并不是想象中的那样，统计每一列的值，而是直接忽视掉所有列，直接统计行数，那么它的效率肯定是很高的。
但是有一点，当col指定了该字段为NOT NULL时实际上，MySQL会自动将count(col)转为count(*),但是这样也同样耗费了些时间，如果col没有指定为NOT NULL的话，那么效率就更低了，MySQL就必须要判断每一行的值是否为空。
所以综上所述，最好优先使用select count(*)
当统计某一列等于多少的值得时候可以使用下面两种方法。
#统计出表中id为23的值的数量的两种方法
SELECT SUM(IF(id = 23,1,0)) FROM table
SELECT COUNT(id = 23 OR NULL) FROM table //为了让id不为23的时候，结果为null,而count(null)=0

解释:

查了下资料，count('任意内容')都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)=0，

因此前者单引号内不管输入什么值都会统计出所有记录数，所以count(id='23')单引号里你填什么年份都是1000条记录，因为他们都not null！

而为了让查找id=23的这个条件生效，就必须加上or null，告诉count，id=23为计数条件且不统计记录为null的值。

Count(NULL)是不统计数，至于加上or NULL ，很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式。当id不为23的时候,id= 23 or NULL 的结果是NULL，Count就不会统计上这条记录数。

又如:

SELECT ID FROM EPRJ_LIST WHERE EPRJ_INFO_ID = 78821 AND (NUM >10 OR NULL)

该SQL语句也是查询NUM >10而且NUM不为NULL的语句(纯粹是为了测试,可以不加or null条件的),因为WHERE条件中如果直接写成:WHERE 条件1 AND NULL 或者WHERE NULL(即以NULL作为一个单独的查询条件),那么返回的结果为空集

而count(id> 23 or null)可以这么理解:SELECT COUNT(*) FROM ...WHERE (id> 23 or null)

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())的例子,：

select
  sum( if( qty > 0, qty, 0)) as total_qty,//用来计算qty >0的所有记录的字段qty值的汇总(从0累加,每次累加qty)
   sum( if( qty < 0, 1, 0 )) as negative_qty_count//计算qty < 0的记录条数(从0累加,每次累加1)
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，

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

参考博客：

http://blog.chinaunix.net/uid-7692530-id-2567582.html

转载于:https://my.oschina.net/u/2331760/blog/1603406