【MySQL】count使用姿势

数据表描述

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的都统计。