PostgreSQL - 如果数据为空，则将数据计为零（使用 where 子句时）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21405207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - count data as zero if it is null (when where clause is used)
提问by Andrius
Now I have 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)
GROUP BY
name
And it outputs this result:
它输出这个结果：
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
Then it outputs this:
然后它输出这个：
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
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).
有可能这样做吗？PS如果需要更多信息，比如把这个查询示例放到网上查看，只需写评论，我会更新我的答案）。
回答by a_horse_with_no_name
Your where condition on the outer joined table turns the outer join into an inner join (because the "non-existing rows will have a NULLvalue and the comparison of NULLwith something else yields "undefined" and thus will remove that row from the result)
外部连接表上的 where 条件将外部连接转换为内部连接（因为“不存在的行将有一个NULL值，并且NULL与其他内容的比较会产生“未定义”，因此将从结果中删除该行）
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;
回答by Hyman
If you want return zero when the result is null, you can use the comand COALESCE.
如果你想在结果为空时返回零，你可以使用命令 COALESCE。
SELECT
opp.name AS name,
COALESCE(COUNT(log.stage_id)),0) 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
It return "0" when count get a null value.
当计数获得空值时，它返回“0”。
