舉例說(shuō)明
從users表里查詢性別為男，省份分別為山西的用戶數(shù)吻氧。
select count(sex = 'man'), count(province = '山西') from users
此時(shí)發(fā)現(xiàn)，兩個(gè)結(jié)果相同咏连，并且是數(shù)據(jù)庫(kù)總記錄的個(gè)數(shù)盯孙。
解決辦法：
select count(sex = 'man' OR NULL), count(province = '山西' OR NULL) from users
理解：
- 當(dāng)
sex不是男時(shí)，sex = 'man'結(jié)果為false,并不是NULL祟滴， - 查詢結(jié)果為
false或者true振惰，count都會(huì)統(tǒng)計(jì)出所有記錄數(shù)，只有遇到null的情況下才會(huì)不計(jì)數(shù)垄懂，count(null) == 0骑晶。 - 加上
OR NULL，像其他or運(yùn)算符一樣草慧，第一個(gè)表達(dá)式是true就是不執(zhí)行or后面的表達(dá)式桶蛔，第一個(gè)表達(dá)式是false就需要執(zhí)行or后面的語(yǔ)句 。 - 當(dāng)
sex = 'man'時(shí),sex = 'man' or NULL的結(jié)果是NULL冠蒋，Count才不會(huì)統(tǒng)計(jì)上這條記錄數(shù)
使用如：
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
