PostgreSQL: 多个条件的多重左连接

Question

以下是我的数据模型的摘录（包括表格内容的摘录）。

我需要统计2015年类型1操作的数量。我还希望在结果中列出完整的城镇列表，而不仅仅是在“operation”表中引用的城镇（对于没有注册操作的城镇，数字为零）。然后我需要指定几个条件，但是“WHERE”子句将我的“LEFT JOIN”转换为“INNER JOIN”（请参见此帖子），因此我必须在“ON”子句中指定条件。

SELECT
  town.town_code,
  count(operation.*) AS nb

FROM town
  LEFT JOIN operation ON town.town_code = operation.ope_town AND operation.ope_year = 2015
  LEFT JOIN intervention ON operation.ope_id = intervention.int_ope_id
  LEFT JOIN nature ON intervention.int_id = nature.int_id AND nature.type_id = 1

GROUP BY town.town_code ORDER BY town.town_code ;

我得到了以下结果：

town_code   | nb
------------+-----
86000       | 1
86001       | 0
86002       | 1
86003       | 1
86004       | 0
86005       | 0

镇区代码86003存在问题，应该为0。这个镇区代码指的是一个操作（#5），该操作又指向一个干预措施（#16），该干预措施涉及到一个自然类型=3。因此，其中一个条件未满足...

如何处理ON子句中的多个条件？

---

编辑：以下是创建表并进行测试的脚本。

CREATE TABLE town (town_code INTEGER, town_name CHARACTER VARING(255)) ;
CREATE TABLE operation (ope_id INTEGER, ope_year INTEGER, ope_town INTEGER) ;
CREATE TABLE intervention (int_id INTEGER, int_ope_id INTEGER) ;
CREATE TABLE nature (int_id INTEGER, type_id INTEGER) ;

INSERT INTO town VALUES (86000, 'Lille'), (86001, 'Paris'), (86002, 'Nantes'), (86003, 'Rennes'), (86004, 'Marseille'), (86005, 'Londres') ;
INSERT INTO operation VALUES (1, 2014, 86000), (2, 2015, 86000), (3, 2012, 86001), (4, 2015, 86002), (5, 2015, 86003) ;
INSERT INTO intervention VALUES (12, 1), (13, 2), (14, 3), (15, 4), (16, 5) ;
INSERT INTO nature VALUES (12, 1), (13, 1), (14, 3), (15, 1), (16, 3) ;

- wiltomap

---

当您将表格发布为图像时，使得任何人回答都变得非常困难。有人该如何尝试这个查询？ - e4c5

刚刚编辑了我的帖子，并添加了创建表和测试的脚本，抱歉。 - wiltomap

Answer 1

这是因为您选择了首个左连接。例如，您可以使用以下代码：

SELECT t.town_code, count(j.*) AS nb FROM town t
  LEFT JOIN (SELECT o.ope_town cd, o.ope_year yr FROM operation o, intervention i, nature n
             WHERE o.ope_year = 2015 
             AND o.ope_id = i.int_ope_id AND n.type_id = 1 
             AND i.int_id = n.int_id) j 
             ON j.cd = t.town_code
 GROUP BY t.town_code ORDER BY t.town_code;

- Shtol Krakov