举例说明
从users表里查询性别为男，省份分别为山西的用户数。
select count(sex = 'man'), count(province = '山西') from users
此时发现，两个结果相同，并且是数据库总记录的个数。
解决办法：
select count(sex = 'man' OR NULL), count(province = '山西' OR NULL) from users
理解：
- 当
sex不是男时，sex = 'man'结果为false,并不是NULL， - 查询结果为
false或者true，count都会统计出所有记录数，只有遇到null的情况下才会不计数，count(null) == 0。 - 加上
OR NULL，像其他or运算符一样，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false就需要执行or后面的语句 。 - 当
sex = 'man'时,sex = 'man' or NULL的结果是NULL，Count才不会统计上这条记录数
使用如：
SELECT
DATE_FORMAT( created_at, '%Y-%m' ) AS day,
biz_user_id AS biz_user_id,
biz_username AS biz_username,
COUNT( DISTINCT client_id ),
count( card_type = 'multi' OR card_type = "single" OR NULL ),
count( card_type = '1in' OR card_type = '2in' OR NULL ),
sum( CASE WHEN card_type = 'multi' OR card_type = "single" THEN sharing_price END ),
sum( CASE WHEN card_type = '1in' OR card_type = '2in' THEN sharing_price END ),
count( DISTINCT CASE WHEN card_type = 'multi' OR card_type = 'single' THEN pay_user_id END ),
count( DISTINCT CASE WHEN card_type = '1in' OR card_type = '2in' THEN pay_user_id END )
FROM
pay_orders
WHERE
state IN ( 'finished', 'refund_pending', 'refund_failed', 'refund_succeed' )
AND created_at >= "2018-01-01 00:00:00"
AND created_at <= "2018-10-31 23:59:59"
GROUP BY
day,
biz_user_id,
biz_username
ORDER BY
DAY,
biz_user_id,
biz_username
