如何显示我的表中不存在的数据的计数(*) im mysql

Question

我有以下查询

select count(1), is_purchased
from game_engine.temp 
group by is_purchased;

结果如下

count(1), is_purchased
1022, 1

它只显示表中存在的is_purchase如果它不存在则显示为下面的

count(1), is_purchased
1022, is Purchased
0, not Purchased

Answer 1

也许最简单的方法是union all:

select count(1), 1 as is_purchased
from game_engine.temp 
where is_purchased = 1
union all
select count(1), 0 as is_purchased
from game_engine.temp 
where is_purchased = 0;

另一种选择是使用left join:

select is_purchased, count(t.is_purchased)
from (select 0 as is_purchased union all select 1 as is_purchased
) x left join
game_engine.temp t
using (is_purchased)
group by is_purchased;