mysql - 是否可以一次加入多对多和一对多？

Question

我的表具有以下架构:wp_职业

wp_locations

wp_careers_locations

wp_educations

职业申请者可以申请很多地点，并且有很多教育记录。

期望的结果是从 wp_careers 中获取所有记录并将申请的位置分组为位置字段，并将所有教育记录 (wp_educations) 作为数组附加到申请人。

现在我知道如何加入多对多关系并对位置进行分组:

  SELECT c.*, GROUP_CONCAT(l.name) as locations
  FROM wp_careers c
  JOIN wp_careers_locations cl ON c.id = cl.career_id
  JOIN wp_locations l ON cl.location_id = l.id
  GROUP BY c.id

但我不知道如何扩展此查询以包含教育记录。

Answer 1

一种方法是再次加入:

SELECT c.*, GROUP_CONCAT(DISTINCT l.name) as locations,
  GROUP_CONCAT(DISTINCT e.institute) AS edu_institutes
FROM wp_careers c
LEFT JOIN wp_careers_locations cl ON c.id = cl.career_id
LEFT JOIN wp_locations l ON cl.location_id = l.id
LEFT JOIN wp_educations e ON c.id = e.career_id
GROUP BY c.id

但这很可能会创建一个 Cartesian product ，因为它会无意中将每个位置加入每个教育。因此，如果您针对给定的职业有三个地点和两种教育，它会在您没有预料到的情况下生成 3x2 = 6 行。我试图用 DISTINCT 对此进行补偿，因此每个 GROUP_CONCAT() 中的名称列表将消除重复项。

但老实说，我更愿意运行两个查询。一个用于位置，第二个查询用于教育。这将避免笛卡尔积。 MySQL 并没有弱到无法处理额外的查询，而且它实际上可能比执行 DISTINCT 操作更便宜。

---

回复你的评论:

您想将教育查询中的职业限制为至少有一个地点的职业吗？

您可以使用半连接来做到这一点:

SELECT c.*, GROUP_CONCAT(e.institute) AS edu_institutes
FROM wp_careers c
JOIN wp_educations e ON c.id = e.career_id
WHERE c.id IN (SELECT career_id FROM wp_career_locations)
GROUP BY c.id

即使 wp_career_locations 中可能有多行与每个相应的 c.id 匹配，它也不会导致笛卡尔积。