数据表描述
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的都统计。
（有什么问题欢迎指教，QQ:2172243813 更多资讯尽在www.ttkmwl.com）
