MYSQL 多组连接

Question

您好，我有一个名为 Engineers 的表和一个名为 Post_Codes 的表

当我使用以下 sql 时，我通过使用 Group Concat 语句获得了工程师列表以及与他们关联的邮政编码，但我不知道如何也包含在另一个 Group Concat 中(如果我确实需要一个)来列出在另一个名为Secondary_Post_Codes_Assigned 的字段中，这些邮政编码通过Secondary_Engineer_id 字段链接到同一工程师。

SELECT 
    Engineer.Engineer,GROUP_CONCAT(Post_Code SEPARATOR ', ') as Post_Codes_Assigned,
    Engineer.Region, 
    Engineer.active, 
    Engineer.Engineer_id
FROM Engineer INNER JOIN Post_Code ON Engineer.Engineer_id = Post_Code.Engineer_id 
GROUP BY Engineer_id 

我需要的是与此类似的输出。

Engineer_id | Post_Codes_Assigned | Secondary_Post_Codes_Assigned


----------
1           | AW, AW3             | B12                          |
2           | B12                 | AW, CV12                     |                

我希望这一点很清楚，因为我对 mysql 还很陌生。

问候艾伦

Answer 1

您已经加入主要邮政编码并列出它们，现在对次要邮政编码执行相同的操作。

SELECT 
  e.Engineer,
  GROUP_CONCAT(DISTINCT pc1.Post_Code) AS Primary_Post_Codes_Assigned,
  GROUP_CONCAT(DISTINCT pc2.Post_Code) AS Secondary_Post_Codes_Assigned,
  e.Region, 
  e.active, 
  e.Engineer_id
FROM Engineer e
JOIN Post_Code pc1 ON e.Engineer_id = pc1.Engineer_id 
JOIN Post_Code pc2 ON e.Engineer_id = pc2.Secondary_Engineer_id
GROUP BY e.Engineer_id;

如您所见，您需要DISTINCT，因为在选择所有主要和所有次要邮政编码时，您将在中间结果中获取它们所有组合的行。所以你必须去掉重复项。因此，最好在加入之前进行聚合。 (我通常认为这是一个好主意，因此您可能希望在使用聚合时养成此习惯。)

SELECT 
  e.Engineer,
  pc1.Post_Codes AS Primary_Post_Codes_Assigned,
  pc2.Post_Codes AS Secondary_Post_Codes_Assigned,
  e.Region, 
  e.active, 
  e.Engineer_id
FROM Engineer e
JOIN 
(
  SELECT Engineer_id, GROUP_CONCAT(Post_Code) AS Post_Codes
  FROM Post_Code
  GROUP BY Engineer_id
) pc1 ON e.Engineer_id = pc1.Engineer_id 
JOIN 
(
  SELECT Secondary_Engineer_id, GROUP_CONCAT(Post_Code) AS Post_Codes
  FROM Post_Code
  GROUP BY Secondary_Engineer_id
) pc2 ON e.Engineer_id = pc2.Secondary_Engineer_id;

第三个选项是SELECT 子句中的子查询。我通常更喜欢将它们放在 FROM 子句中，如图所示，因为这样很容易向子查询添加更多列，而这在 SELECT 子句中是不可能的。

SELECT 
  e.Engineer,
  (
    SELECT GROUP_CONCAT(pc1.Post_Code)
    FROM Post_Code pc1
    WHERE pc1.Engineer_id = e.Engineer_id
  ) AS Primary_Post_Codes_Assigned,
  (
    SELECT GROUP_CONCAT(pc2.Post_Code)
    FROM Post_Code pc2
    WHERE pc2.Secondary_Engineer_id = e.Engineer_id 
  ) AS Secondary_Post_Codes_Assigned,
  e.Region, 
  e.active, 
  e.Engineer_id
FROM Engineer e;