PostgreSQL的计数(布尔表达式)
问题描述:
什么是PostgreSQL中的最佳方式,我想在一个领域与value = 1
算多少算多少数与value = 0
在同一领域A.PostgreSQL的计数(布尔表达式)
是这样的:
select
count (field1 = value1) as filed1_ok,
count (field1 = value2) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
答
使用count(condition or null)
select
count(field1 = 1 or null) as filed1_ok,
count(field1 = 0 or null) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
true or null
评估为true
。 false or null
评估为null
。由于count
不计算正是你想要的空值。
在其他SQL方言,并在PostgreSQL中可以使用case
select
coalesce(sum(case field1 when 1 then 1 end), 0) as filed1_ok,
coalesce(sum(case field1 when 0 then 1 end), 0) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
我认为这是冗长和不透明相比count(condition or null)
PostgreSQL的选项来完成。