sql - PostgreSQL - 如何在此查询中显示 count = 0？

Question

避难所库存(表格)

gid | pav_type | yes
 1  |  1       | 1
 2  |  1       | 1
 3  |  0       | 1
 4  |  2       | 1 
 5  |  2       | 0

这是当前查询(不显示count = 0)

SELECT pav_type, count(*) FROM shelter_inventory 
WHERE yes = 1 GROUP BY pav_type ORDER BY pav_type

我希望结果像这样显示

pav_type | count(*)
 0       |  1
 1       |  2
 2       |  1 
 3       |  0

我该如何查询这个案例？我使用 PostgreSQL。

Answer 1

如果您的 pav_type 值是连续的，那么您可以使用 generate_series代替外部表:

select p.pav_type, count(yes)
from generate_series(0,3) as p(pav_type)
left outer join shelter_inventory s
  on p.pav_type = s.pav_type and s.yes = 1
group by p.pav_type
order by p.pav_type

这会产生:

 pav_type | count 
----------+-------
        0 |     1
        1 |     2
        2 |     1
        3 |     0

这个:

generate_series(0,3) as p(pav_type)

基本上生成一个内联表，其中包含名为 pav_type 的单列和四行:0、1、2、3。并且您需要让 s.yes = 1 在连接条件(而不是 WHERE)中，因为您希望 yes = 0 值在预先分组的结果集中；如果 s.yes = 1 在 WHERE 子句中，则无论连接条件是什么，都不会计算 yes = 0 行。

如果您的 pav_types 不能很好地适应 generate_series(即不是顺序的或步进顺序的)并且您只有一小部分，您可以加入一个VALUES expression :

select p.pav_type, count(yes)
from (values (0), (1), (2), (3)) as p(pav_type)
left outer join shelter_inventory s
  on p.pav_type = s.pav_type and s.yes = 1
group by p.pav_type
order by p.pav_type

当然，您需要确保将所有括号放在正确的位置。

如果您在单独的表中确实有 pav_types，则对该表执行 LEFT OUTER JOIN 而不是使用 generate_series。如果您的 pav_types 不是顺序的，并且您有太多的东西无法明智地放入 VALUES 表达式中，那么构建一个表来保存有效的 pav_type 值和 LEFT OUTER JOIN 到它。