sql - 在具有条件的多对多表中执行左连接

Question

一般情况

当您想向关系的外部添加条件时，如何在多对多关系中执行左连接？

具体案例

我们正在处理两个表:team 和 pool。还有一个 team_pool 表作为它们之间的多对多联结表。此外，pool 有一个 stage_id 列。

我想在特定阶段检索具有该团队池的所有团队。如果池不存在，我希望池为 NULL。样本，理想化的结果:

+--------+----------+------+
| team   | stage_id | pool |
+--------+----------+------+
| Team 1 |        2 | C    |
| Team 2 |     NULL | NULL | //this team doesn't belong to a pool for this stage (but it could belong to a pool for a different stage)
| Team 3 |        2 | G    |
| Team 3 |        2 | H    | //if a team belongs to a 2 pools for the same stage
| Team 4 |        2 | D    |

如果相关，我正在使用 MySQL。

SQL

这是用于创建表的(简化的)SQL:

CREATE TABLE team (id BIGINT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id));
CREATE TABLE team_pool (team_id BIGINT, pool_id BIGINT, PRIMARY KEY(team_id, pool_id));
CREATE TABLE pool (id BIGINT AUTO_INCREMENT, stage_id BIGINT, name VARCHAR(40) NOT NULL, PRIMARY KEY(id));

理想的解决方案

理想的解决方案是:

• 不需要更改我的架构(ORM 确实希望这样)
• 需要单个非 UNION 查询。

尝试的解决方案

• 使用 INNER JOIN 而不是 LEFT JOIN 从 team 到 team_pool 和 team_pool 到 pool。问题:我们失去了不属于池的团队
• LEFT JOIN 从 team 到 team_pool 和 team_pool 到 pool 使用 WITH 条件 pool 上的 stage_id 与我们正在寻找的相匹配。问题:当一个团队属于多个池时，我们会得到多个结果。添加 GROUP BY 没有帮助。

编辑:选择的解决方案

这里有很多好的解决方案。

鉴于我的理想解决方案是不可能的，我宁愿将 stage_id 添加到 team_pool 而不是使用 UNION 或子查询。这有一个额外的好处，让我强制一个团队在每个阶段只能属于一个池。它还使查询变得简单:

SELECT t.name, p.name, tp.stage_id FROM team t LEFT JOIN team_pool tp ON t.id = tp.team_id AND tp.stage_id = 2 LEFT JOIN pool p ON p.id = tp.pool_id

Answer 1

如果我理解您的架构背后的概念，那么我认为 stage_id 应该是 team_pool 中的一个列，而不是 pool。阶段不是池的属性，它是将团队映射到池的一个因素，对吧？

无论如何，这就是我在 Oracle 中编写查询的方式。我不确定这个确切的语法是否适合 MySQL。假设您想要参数化 stage_id 的字面值。

SELECT t.name, p.name
  FROM (SELECT team.name, pool_id
          FROM team LEFT JOIN team_pool
            ON team_pool.team_id = team.team_id ) t
       LEFT JOIN (SELECT pool_id, name FROM pool WHERE stage_id = 2) p
            ON p.pool_id = t.pool_id