mysql - 即使计数值为0 mysql也选择行

Question

select c.id, count(*) as 'Transactions' from voucher v 
right join consumer c on v.consumer_id = c.id
where v.voucher_state in ('REDEEMED', 'EXPIRED') and c.country = 'INDIA'
group by c.id;

预期输出:-

Id 交易

3 0

4 0

6 3

7 9

8 4

9 0

当前输出:-

Id 交易

6 3

7 9

8 4

如何选择计数为 0 的行？谢谢。

Answer 1

您必须使用LEFT JOIN，将consumer 表放在左侧并移动

 v.voucher_state in ('REDEEMED', 'EXPIRED'):

ON 子句:

select c.id, count(v.consumer_id) as 'Transactions' 
from consumer c
left join voucher v  on v.consumer_id = c.id on v.voucher_state in ('REDEEMED', 'EXPIRED')
where c.country = 'INDIA'
group by c.id;

以上查询将返回所有 customer.id 满足条件的值

customer.country = 'INDIA'

只有在 voucher 表中有匹配记录的客户

voucher.voucher_state in ('REDEEMED', 'EXPIRED')

将被计算在内。