postgresql - 在具有多个子类别的 postgres 中使用 COUNT(*) 返回 0 值

Question

我无法让 COUNT(*)=0 显示在列中。此问题已在此处在某种程度上得到解决:how to make this query also return rows with 0 count value?

...但是我无法将解决方案推广到多个不同的类别。这是我的情况:我有 11 个不同类别的 parking 位置和 4 个不同类别的从属关系:

    # SELECT DISTINCT parking_location FROM respondents;     parking_location     ------------------     on-street free     city garage     UC lot     rpp visitor     off-street free     other     nowhere     other paid     meter     disabled     rpp    (11 rows)    # SELECT DISTINCT affiliation FROM respondents;     affiliation     -------------     faculty     undergrad     grad     staff    (4 rows)

我的本​​科生受访者都没有使用残疾人 parking 位，所以当我尝试按 parking_location 计算他们时，我只返回 10 行:

    SELECT parking_location,COUNT(*) FROM respondents WHERE affiliation='undergrad' GROUP BY parking_location;     parking_location | count     ------------------+-------     on-street free   |     2     meter            |    25     city garage      |     5     rpp              |    21     nowhere          |  1012     UC lot           |    33     rpp visitor      |    10     off-street free  |    10     other            |    10     other paid       |    12    (10 rows)

没问题。上述链接显示了如何使 0 出现:

    ths=# WITH c as (SELECT DISTINCT parking_location FROM respondents),    ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents WHERE affiliation='undergrad' GROUP BY 1,2)    ths-# SELECT c.parking_location, COALESCE(r.count, 0) AS count FROM c    ths-# LEFT JOIN r ON c.parking_location = r.parking_location    ths-# ORDER BY parking_location;     parking_location | count     ------------------+-------     nowhere          |  1012     meter            |    25     rpp              |    21     rpp visitor      |    10     on-street free   |     2     UC lot           |    33     off-street free  |    10     city garage      |     5     other paid       |    12     disabled         |     0     other            |    10    (11 rows)

但现在我想显示所有从属关系的表格，而不仅仅是本科生。此外，我想先按从属关系然后按 parking_location 对结果表进行排序。我以为我可以删除上面的 WHERE 子句，但后来我的 undergrad disabled 列消失了:

    ths=#  WITH c as (SELECT DISTINCT parking_location FROM respondents),     ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents GROUP BY affiliation,parking_location)    ths-# SELECT r.affiliation, c.parking_location, COALESCE(r.count, 0) FROM c    ths-# LEFT JOIN r ON c.parking_location = r.parking_location    ths-# ORDER BY affiliation,parking_location;     affiliation | parking_location | coalesce     -------------+------------------+----------         staff       | city garage      |       34     staff       | other paid       |       50     staff       | disabled         |       18     staff       | other            |       61     undergrad   | nowhere          |     1012     undergrad   | meter            |       25     undergrad   | rpp              |       21     undergrad   | rpp visitor      |       10     undergrad   | on-street free   |        2     undergrad   | UC lot           |       33     undergrad   | off-street free  |       10     undergrad   | city garage      |        5     undergrad   | other paid       |       12     undergrad   | other            |       10     grad        | nowhere          |     1113     grad        | meter            |       96     grad        | rpp              |       31    

有什么帮助吗？

Answer 1

尝试这样的事情:

WITH all_parking_locations as (SELECT DISTINCT parking_location 
                               FROM respondents),

     all_affiliations as  (SELECT DISTINCT affiliation 
                           FROM respondents),

     all_counts as (SELECT affiliation, parking_location, COUNT(*) AS count 
                    FROM respondents 
                    GROUP BY affiliation, parking_location)

SELECT aa.affiliation, apl.parking_location, COALESCE(ac.count,0) as count
FROM all_affiliations aa
CROSS JOIN all_parking_locations apl
LEFT JOIN all_counts ac ON ac.affiliation = aa.affiliation
                       AND ac.parking_location = apl.parking_location
ORDER BY aa.affiliation, apl.parking_location