sql - 连接三张表，主表在中间

Question

我在连接三个表时伤透了脑筋。我重新创建了一个简单的测试用例，我发现了同样的问题，所以看起来我在连接查询中犯了一个根本性的错误:

我有三个表:

case:
id (PK)| date_closed
155    | '2018-04-17 10:08' 
156    | '2018-03-17 10:08'     
pizza  | '2018-02-17 10:08' 

registration:
id (FK) | source | quantity
155     | market | 300
155     | sawdust| 200

bagged:
id | case_id (FK) | kg_bagged
X  | 155          | 123
Y  | 155          | 90

我想加入这些表以比较数量列和 kg_bagged 中每个“案例”的总量。所以 case 表与其他两个表有 1:* many 关系。因此我做了一个这样的连接查询:

SELECT case.id, 
       date_closed,
       SUM(quantity),
       SUM(kg_bagged),
       SUM(kg_bagged)/SUM(quantity) AS reduction_factor
FROM case
    JOIN bagged ON case.id = bagged.case_id
    JOIN registration ON case.id = registration.id

我认为这是一个正确的查询，但 Postgres 告诉我必须将 case.id、date_closed 添加到 group by 子句中。所以我添加这个:

GROUP BY case.id, date_closed;

此代码运行时没有错误，但在案例 155 中显示的数量为 1000，而不是预期的 500 (200+300)。仅当有超过 1 条记录时才会出现此行为。当仅将 1 个表连接到案例表时，它也可以正常工作。有人可以看到在 JOIN 查询中犯的错误吗？

我也尝试过使用子查询来连接两个表，而不是在左边的表上使用连接，但它给了我类似的结果

Answer 1

当您连接 2 个其他表中的 2 行数据时，它们会匹配在一起，因此您会得到相乘的结果。在你的例子中是 2*2 = 4

为了更容易理解，在你执行查询的情况下

 SELECT case.id, date_closed, source, quantity, kg_bagged
   FROM case
   JOIN registration ON registration.id = case.id
   JOIN bagged ON bagged.case_id = case.id

你会得到这样的数据:

| id  | date_closed        | source | quantity | kg_bagged |
| :-: | :----------------: | :----: | :------: | :-------: |
| 155 | '2018-04-17 10:08' | market | 300      | 123       |
| 155 | '2018-04-17 10:08' | sawdust| 200      | 123       |
| 155 | '2018-04-17 10:08' | market | 300      | 90        |
| 155 | '2018-04-17 10:08' | sawdust| 200      | 90        |

在这种情况下，根据我之前的经验，我习惯先写子查询，先获取总和数据，然后再将它们连接在一起。

如:

   WITH r AS (SELECT id, sum(quantity) as quantity FROM registration GROUP BY id),
    b as (SELECT case_id, SUM(kg_bagged) as kg_bagged FROM bagged GROUP BY case_id)
 SELECT case.id, 
    date_closed,
    quantity,
    kg_bagged,
    kg_bagged/quantity AS reduction_factor
   FROM case
   JOIN b ON case.id = b.case_id
   JOIN r ON case.id = r.id

希望这个答案对您有所帮助。