【MySQL】count使用姿势

小鱼儿 2022-06-10 12:51 173阅读 0赞

数据表描述

mysql> select vip,port,status from vip_full limit 10;
    +----------------+------+--------+
    | vip            | port | status |
    +----------------+------+--------+
    | 123.125.52.231 |   80 |      0 |
    | 10.143.153.101 | 1986 |      1 |
    | 123.125.52.231 |  443 |      1 |
    | 10.143.153.71  | 5477 |      1 |
    | 10.143.153.71  | 5480 |      0 |
    | 10.143.153.72  | 5481 |      1 |
    | 10.143.153.71  | 5495 |      1 |
    | 10.143.153.71  | 5492 |      0 |
    | 10.143.153.71  | 5493 |      0 |
    | 10.143.153.71  | 5497 |      1 |
    +----------------+------+--------+
    10 rows in set

status=0表示未完成，1表示完成。

要统计一个vip下有多少完成的数量，有多少未完成的数量(附加条件，完成数量大于0，未完成数量大于0)

第一次写下了这么一个语句

mysql> select count(status=1) as status1,count(status=0) as status0,vip from vip_full group by vip having status1>0 and status0>0 limit 10;
    +---------+---------+---------------+
    | status1 | status0 | vip           |
    +---------+---------+---------------+
    |       1 |       1 | 1.192.137.217 |
    |       2 |       2 | 1.192.137.230 |
    |       1 |       1 | 1.192.137.232 |
    |       2 |       2 | 1.192.137.234 |
    |       2 |       2 | 1.192.137.244 |
    |       1 |       1 | 1.192.137.245 |
    |       2 |       2 | 1.192.137.246 |
    |       1 |       1 | 1.192.137.247 |
    |       1 |       1 | 1.192.137.248 |
    |       1 |       1 | 1.192.137.249 |
    +---------+---------+---------------+
    10 rows in set

检验：

mysql> select vip,port,status from vip_full where vip="1.192.137.217";
    +---------------+------+--------+
    | vip           | port | status |
    +---------------+------+--------+
    | 1.192.137.217 |   80 |      0 |
    +---------------+------+--------+
    1 row in set

从上面的查询接口可以看出，统计数据中，vip为1.192.137.217，status=0和1的应该各为1个啊，而检验中，该vip下只有一个未完成的，并没有完成的。

可以看出，第一次count的数据是不正确的，正确语句应该如下

mysql> select count(status=1 or null) as status1,count(status=0 or null) as status0,vip from vip_full group by vip having status1>0 and status0>0 limit 10;
    +---------+---------+----------------+
    | status1 | status0 | vip            |
    +---------+---------+----------------+
    |      36 |       1 | 10.131.199.249 |
    |      75 |       1 | 10.131.199.254 |
    |       1 |       1 | 10.138.245.78  |
    |     173 |       1 | 10.139.230.127 |
    |       2 |       1 | 10.139.230.68  |
    |      37 |       1 | 10.142.234.19  |
    |      50 |       1 | 10.142.234.20  |
    |     152 |       1 | 10.143.153.107 |
    |      39 |       1 | 10.143.153.186 |
    |       2 |       1 | 10.143.153.63  |
    +---------+---------+----------------+
    10 rows in set

检验：

mysql> select * from vip_full where vip = "10.143.153.63";
    +-------+------+---------------+------+-----------+----------------------------+----------------------+--------+---------------+-------+
    | id    | idc  | vip           | port | sms_alarm | email_alarm                | department           | status | sbusiname     | istag |
    +-------+------+---------------+------+-----------+----------------------------+----------------------+--------+---------------+-------+
    | 46489 | bjcc | 10.143.153.63 |   80 | add_ops   | sys_weblvs_alarm_emailonly | innerlbs.huajiao.com |      1 | 花椒 附近的人 |     0 |
    | 46551 | bjcc | 10.143.153.63 | 8088 | add_ops   | sys_weblvs_alarm_emailonly | innerlbs.huajiao.com |      0 | NULL          |     1 |
    | 46552 | bjcc | 10.143.153.63 | 8888 | add_ops   | sys_weblvs_alarm_emailonly | innerlbs.huajiao.com |      1 | 花椒 附近的人 |     0 |
    +-------+------+---------------+------+-----------+----------------------------+----------------------+--------+---------------+-------+
    3 rows in set

结果正确，解释如下

因为 当 status 不是 0 时 ，status=0 结果false 不是 NULL，

Count在 值是NULL是 不统计数， 至于加上or NULL ， 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。当 status 不为 0 时 status = 0 or NULL 的结果是NULL，Count才不会统计上这条记录数,否则会不管是不是0的都统计。