Postgres 中的左外连接不返回空值的值

Question

一个下载包括下载时间、下载时间id和buno ID。 故障包括故障代码、下载时间id、状态和类型。一个下载可以有很多故障，并且可以根据下载时间id进行连接。

给定一组故障代码，结果必须包含每个故障代码及其相应的故障计数。如果在下载中未找到故障代码，则必须返回故障代码以及故障计数为零。

该问题似乎需要使用外部连接，但是在Postgres上看不到它按预期工作，因为它似乎没有从LEFT表返回带有nulls的集合。

以下是查询语句，其中省略了一些详细信息：

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

第二天，我进行了编辑并显示了答案。所有答案都很接近并且提供了各种帮助元素。然而，JayC的回答最接近。这是最终的SQL，唯一的变化是WHERE子句采用故障代码IN语句：

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM    download_time d  
RIGHT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
        AND f.statusid IN(2, 4)
        AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012'
        AND d.bunoid = 166501
WHERE f.faultcode IN (1000,1100)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

感谢大家的帮助！喜欢这个网站！

- MAbraham1

---

你分析过数据了吗？我怀疑这可能不是PostgreSQL的问题...请您提供表结构和样本数据在SQL Fiddle上，好吗？ - vyegorov

download_time d LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id中的左侧表是download_time，而不是fs_fault。连接条件与表在连接中的左右位置无关。 - JayC

Answer 1

我提供自己的答案是因为我对其他答案有很大的疑虑。在筛选条件上必须小心。请记住，where子句在连接之后运行。因此，如果where子句中有任何筛选条件引用非外部连接表，在许多情况下，你会使你的外部连接无效。所以根据你的SQL查询，最简单的解决方案似乎是使用正确的连接或适当移动表名，并将筛选条件从where子句移到join子句中。

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

我认为另一种等同的方法是：

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
WHERE
    f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

由于在 fs_fault 过滤条件的位置并不重要（而且你的 SQL 引擎也会更改它），所以可以灵活设置。

编辑：这里有一个 SQLFiddle，演示了在连接子句与 where 子句中过滤的区别。

- JayC

---

关于WHERE子句和JOIN条件，你说得对。我已经在这方面修正了我的答案。不过，我认为你还没有回答他的问题，即如何使所需的故障代码显示即使没有发生。 - kgrittn

谢谢大家：我给所有回答者投了一票，因为我从每个人那里学到了东西。然而，JayC，你的回答最接近。唯一的改变是将“f.faultcode IN（1000,1100）”移动到where子句中。这样做后，正确的故障代码被显示出来。谢谢，JayC！ - MAbraham1

我无法让SQLFiddle工作。我使用的是IE 7.0.5730.13CO，因为我在工作中。我将不得不在家里尝试它，那里我通常运行Chrome。 - MAbraham1

Answer 2

这将需要一个 RIGHT OUTER JOIN。右外连接包括右表中的所有值，其中左表中没有条目的位置为 NULL（虽然我不确定这是否适用于 GROUP BY...）。如果 fs_fault 是一个包含所有故障代码的表，则成立。在您的情况下，fs_fault 似乎包含了下载的所有故障。这可能是意外行为的原因吗？

- krlmlr

Answer 3

如果您想按故障代码进行计数，这似乎是最简单的解决方案：

WITH fc(faultcode) AS (VALUES (1000,1100))
SELECT fc.faultcode, count(d.downloadtimeid) as faultcount 
  FROM fc
  LEFT JOIN (fs_fault f ON f.faultcode = fc.faultcode
                       AND f.statusid IN(2, 4)
  JOIN download_time d ON d.id = f.downloadtimeid
                      AND d.bunoid = 166501
                      AND d.downloadtime::date BETWEEN date '2011-04-11'
                                                   AND date '2011-05-01')
  GROUP BY fc.faultcode
  ORDER BY fc.faultcode

请注意，我保留了您的条件，如果故障没有正确的状态编号或bunoid，则不计算。我有点担心日期选择可能不是您想要的，因此我提出了一种替代方案。如果您使用的是“TIMESTAMP WITHOUT TIME ZONE”，那么即使采用这种方法，也可能无法达到您想要的效果，但这是另外一个问题。我还添加了一个“ORDER BY”子句，因为您可能不希望结果以不一致的顺序呈现; 没有该子句，结果可能会按照“GROUP BY”序列排序，这可能会在没有警告的情况下发生变化。

- kgrittn

---

如果故障代码没有关联的下载时间，它将被 where 子句过滤掉。 - JayC

哎呀，加上括号以强制执行连接的顺序就解决了。谢谢！ - kgrittn

在他的回答中，@JayC对WHERE子句与JOIN条件提出了一个很好的观点。根据原始写法，如果特定faultcode的所有行都被条件排除掉，那么该faultcode将不会显示。通过移动条件进行修复。 - kgrittn

Answer 4

左外连接选取第一个表中的所有内容以及第二个表中匹配的行。第一个表似乎包含了下载尝试的所有记录。因此，“from”中的结果包括了所有的下载尝试。

但是，这并不一定包含了所有的故障代码。这意味着你没有符合条件的一个或多个代码的故障。

为了使其正常工作，你需要一个包含所有故障代码的表。在这里，我只是将故障代码列表创建为第一个表。我认为以下查询可以实现这一点：

SELECT thefaults.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM  (select 1000 as faultcode union all select 1100
      ) thefaults join
      fs_fault f
      on f.faultcode = thefaults.faultcode and
         f.statusid in (2, 4) left outer join
      download_time d
      ON f.downloadtimeid = d.id
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012') AND
      d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode 

我承认：我正在使用SQL Server语法来创建“thefaults”。

- Gordon Linoff

---

如果故障代码没有关联的下载时间，它将被 where 子句过滤掉。 - JayC

一个人认为这就是提问者想要的。 - Gordon Linoff