使用 LEFT JOIN 查询不返回计数为 0 的行 [英] Query with LEFT JOIN not returning rows for count of 0

问题描述

我正在尝试获取以下内容以返回每个在 PostgreSQL 中使用左连接的组织的计数，但我不知道为什么它不起作用:

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:

  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

使用 coalesce 似乎不起作用.我的智商不行了！任何帮助都将不胜感激！

Using coalesce doesn't seem to work. I'm at my wit's end! Any help would certainly be appreciated!

为了澄清什么不起作用，目前查询仅返回计数大于 0 的组织的值.我希望它为每个组织返回一行，而不管计数如何.

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 every organisation, regardless of the count.

表定义:

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)

推荐答案

修复LEFT JOIN

这应该有效:

Fix the LEFT JOIN

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
GROUP  BY o.name
ORDER  BY o.name;

您有一个 LEFT [OUTER] JOIN，但后来的 WHERE 条件使它表现得像一个普通的 [INNER] JOIN.
将条件移动到 JOIN 子句以使其按预期工作.这样，只有满足所有这些条件的行才会首先加入(或者右表中的列用 NULL 填充).就像您拥有的那样，在 LEFT JOIN 之后几乎 测试连接的行是否有其他条件，如果它们没有通过就将其删除，就像使用普通的 JOIN.

You had a LEFT [OUTER] JOIN but the later WHERE conditions made it act like a plain [INNER] JOIN.
Move the condition(s) to the JOIN clause 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 right table are filled with NULL). Like you had it, joined rows are tested for additional conditions virtually after the LEFT JOIN and removed if they don't pass, just like with a plain JOIN.

count() 从不返回 NULL 开始.在这方面，这是聚合函数中的一个例外.因此， COALESCE(COUNT(col)) 永远 没有意义，即使有额外的参数.手册:

count() never returns NULL to begin with. It's an exception among aggregate functions in this respect. Therefore, COALESCE(COUNT(col)) never makes sense, even with additional parameters. The manual:

需要注意的是，除了count，这些函数在没有选择任何行时返回空值.

It should be noted that except for count, these functions return a null value when no rows are selected.

粗体强调我的.见:

• 数数一行为 NULL 的属性

count() 必须在定义为 NOT NULL(如 e.id)的列上，或者连接条件保证 NOT NULL(e.organisation_id、e.item_template_id 或 e.used)在示例中.

count() must be on a column defined NOT NULL (like e.id), or where the join condition guarantees NOT NULL (e.organisation_id, e.item_template_id, or e.used) in the example.

由于 used 是 boolean 类型，表达式 e.used = true 是一种可以燃烧为 e.used 的噪音.

Since used is type boolean, the expression e.used = true is noise that burns down to just e.used.

由于 o.name 未定义 UNIQUE NOT NULL，您可能需要 GROUP BY o.id 代替 (id 是 PK) - 除非您打算折叠具有相同名称的行(包括 NULL).

Since o.name is not defined UNIQUE NOT NULL, you may want to GROUP BY o.id instead (id being the PK) - unless you intend to fold rows with the same name (including NULL).

如果大部分或所有 exam_items 行都被计算在内，这个等效的查询通常会更快/更便宜:

If most or all rows of exam_items are counted in the process, this equivalent query is typically considerably faster / cheaper:

SELECT o.id, o.name AS organisation_name, e.total_used
FROM   organisations o
LEFT   JOIN (
   SELECT organisation_id AS id   -- alias to simplify join syntax
        , count(*) AS total_used  -- count(*) = fastest to count all
   FROM   exam_items
   WHERE  item_template_id = #{sanitize(item_template_id)}
   AND    used
   GROUP  BY 1
   ) e USING (id)
ORDER  BY o.name, o.id;

(这是假设您不想像上面提到的那样折叠具有相同名称的行 - 典型情况.)

(This is assuming that you don't want to fold rows with the same name like mentioned above - the typical case.)

现在我们可以在子查询中使用更快/更简单的count(*)，并且我们不需要在外部SELECT中使用GROUP BY.

Now we can use the faster / simpler count(*) in the subquery, and we need no GROUP BY in the outer SELECT.

见:

• 单个查询中的多个 array_agg() 调用

这篇关于使用 LEFT JOIN 查询不返回计数为 0 的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！