PostgreSQL - 如果数据为空，则将数据计数为零（当使用where子句时） [英] PostgreSQL - count data as zero if it is null (when where clause is used)

问题描述

现在我有这个查询：

SELECT 
opp.name as name,
count(log.stage_id) as stage_count
FROM 
crm_lead as opp LEFT OUTER JOIN crm_lead_stage_log as log ON (opp.id = log.opportunity_id)
GROUP BY
name

并输出结果：

name  | stage_count |
name1 | 2
name2 | 1
name3 | 0 

它输出我需要的。但是，如果我把任何条件，它跳过行的零计数，我需要能够看到。
例如，如果我写这个查询：

And it outputs what I need. But if I put any condition to it, then it skips rows with zero count, which I need to be able to see. For example if I write this query:

SELECT 
opp.name as name,
count(log.stage_id) as stage_count
FROM 
crm_lead as opp LEFT OUTER JOIN crm_lead_stage_log as log ON (opp.id = log.opportunity_id)
WHERE WHEN log.create_date > '2014-01-28 08:49:03'
GROUP BY
name

然后它输出：

name  | stage_count |
name1 | 1

它正确计算该时间间隔中的现有阶段数，但跳过不是阶段数的行存在于时间不密切。如何使它的输出像这样（在该示例中只有一个阶段的第一行计数在该时间间隔与新的查询，对于其他行，它计数为零，因为它不存在）：

It counts existing stages number in that time interval correctly, but it skips rows which stages number is not existing in the time inerval. How can I make it output like this (in that example only one stage for first row is counted in that time interval with new query, for other rows, it counts zero, because it does not exist):

name  | stage_count |
name1 | 1
name2 | 0
name3 | 0 

可以这样做吗？
P.S.如果需要更多信息，喜欢把这个查询示例在线检查，只是写一个注释，我会更新我的答案。）

Is it possible to do it like that? P.S. if more information is needed, like to put this query sample online to check it out, just write a comment and I will update my answer).

推荐答案

外部联接表上的where条件将外部联接转换为内部联接（因为不存在的行将具有 NULL 值， NULL 与其他的东西产生undefined，因此将从结果中删除该行）

Your where condition on the outer joined table turns the outer join into an inner join (because the "non-existing rows will have a NULL value and the comparison of NULL with something else yields "undefined" and thus will remove that row from the result)

将该条件移动到连接条件中：

You need to move that condition into the join condition:

SELECT opp.name as name,
       count(log.stage_id) as stage_count
FROM crm_lead as opp 
  LEFT JOIN crm_lead_stage_log as log 
         ON opp.id = log.opportunity_id
        AND log.create_date > '2014-01-28 08:49:03'
GROUP BY name;

这篇关于PostgreSQL - 如果数据为空，则将数据计数为零（当使用where子句时）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！