SQL 使用 LEFT JOIN 查询不返回计数为 0 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15467624/
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
Query with LEFT JOIN not returning rows for count of 0
提问by mulus
I am trying to get the following to return a count for every organization using a left join in PostgreSQL, but I cannot figure out why it's not working:
我正在尝试获取以下内容以返回每个在 PostgreSQL 中使用左连接的组织的计数，但我无法弄清楚为什么它不起作用：
select o.name as organisation_name,
coalesce(COUNT(exam_items.id)) as total_used
from organisations o
left join exam_items e on o.id = e.organisation_id
where e.item_template_id = #{sanitize(item_template_id)}
and e.used = true
group by o.name
order by o.name
Using coalescedoesn't seem to work. I'm at my wit's end! Any help would certainly be appreciated!
使用coalesce似乎不起作用。我的智商不行了！任何帮助肯定会受到赞赏！
To clarify what's not working, at the moment the query only returns values for organisations that have a count greater than 0. I would like it to return a line for everyorganisation, regardless of the count.
为了澄清什么不起作用，目前查询仅返回计数大于 0 的组织的值。我希望它为每个组织返回一行，而不管计数如何。
Table definitions:
表定义：
TABLE exam_items
id serial NOT NULL
exam_id integer
item_version_id integer
used boolean DEFAULT false
question_identifier character varying(255)
organisation_id integer
created_at timestamp without time zone NOT NULL
updated_at timestamp without time zone NOT NULL
item_template_id integer
stem_id integer
CONSTRAINT exam_items_pkey PRIMARY KEY (id)
TABLE organisations
id serial NOT NULL
slug character varying(255)
name character varying(255)
code character varying(255)
address text
organisation_type integer
created_at timestamp without time zone NOT NULL
updated_at timestamp without time zone NOT NULL
super boolean DEFAULT false
CONSTRAINT organisations_pkey PRIMARY KEY (id)
回答by Erwin Brandstetter
This should work:
这应该有效：
SELECT o.name AS organisation_name
, COUNT(e.id) AS total_used
FROM organisations o
LEFT JOIN exam_items e ON e.organisation_id = o.id
AND e.item_template_id = #{sanitize(item_template_id)}
AND e.used = true
GROUP BY o.name
ORDER BY o.name;
You had a
LEFT [OUTER] JOINbut the laterWHEREconditions made it act like a plain[INNER] JOIN.
Move the condition(s) to theJOINclause to make it work as intended. This way, only rows that fulfill all these conditions are joined in the first place (or columns from the righttable are filled with NULL). Like you had it, joined rows are tested for additional conditions virtually aftertheLEFT JOINand removed if they don't pass, just like with a plainJOIN.COUNT()never returns NULL to begin with. It's an exception among aggregate functions in this respect. Therefore,COALESCE(COUNT(col))It should be noted that except for
count, these functions return a null value when no rows are selected.
你有 a
LEFT [OUTER] JOIN但后来的WHERE条件使它表现得像一个平原[INNER] JOIN。
将条件移动到JOIN子句以使其按预期工作。这样，只有满足所有这些条件的行才会首先加入（或右表中的列填充为 NULL）。就像你有它，加入行测试几乎附加条件后的LEFT JOIN，如果他们不及格，除去就像一个普通的JOIN。COUNT()从不返回 NULL 开始。在这方面，这是聚合函数中的一个例外。因此，即使使用附加参数，也永远没有意义。手册：COALESCE(COUNT(col))需要注意的是，除了 之外
count，当没有选择任何行时，这些函数返回一个空值。
Bold emphasis mine.
Realted:
大胆强调我的。
房地产：
回答by Sruit A.Suk
To make it clear,
为了清楚起见，
the important line is GROUP BY MAIN_TABLEthat will handle NULL value from SOME_TABLE
重要的一行是GROUP BY MAIN_TABLE将处理来自SOME_TABLE
SELECT COUNT(ST.ID)
FROM MAIN_TABLE MT
LEFT JOIN SOME_TABLE ST ON MT.ID = ST.MT_ID
GROUP BY MT.ID -- this line is a must
