在MYSQL中连接引用表1的3个表并用逗号分隔符组合值

Question

我在连接三个表时遇到问题。 第一次尝试连接两个表是成功的，但在第三个表上结果不正确......

我有三个表ma​​chine_list是mainTable，然后applicable_rpm和applicable_product是machine_list的一些详细信息

表：ma​​chine_list

| id | machine_number | machine_brand |
---------------------------------------
| 1  |     MN-1       |     TOYO      |
| 2  |     MN-2       |    AMITA      |

表：applicable_rpm

| id | mc_recordID | rpm |
--------------------------
| 1  |      1      | 20  |
| 2  |      2      | 20  |
| 3  |      2      | 25  |

表格：applicable_product

| id | mc_recordID | productline|
---------------------------------
| 1  |      1      |    mono    |
| 2  |      2      |    mono    |
| 3  |      2      |    poly    |

我想这样返回：

| machine_number |   rpm  |   twine    |
----------------------------------------
|      MN-1      | 20     | mono       |
|      MN-2      | 20, 25 | mono, poly |

我首先尝试使用以下查询连接两个表：

SELECT t1.machine_number, GROUP_CONCAT(' ', t2.speed) machine_speed
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
GROUP BY t1.id;

结果是：

| machine_number |  rpm   |
---------------------------
|      MN-1      |  20    |
|      MN-2      | 20, 25 |

这是正确的，但是当我尝试第三个表时，它重复了它的值。

这是我的查询：

SELECT t1.machine_id,
       GROUP_CONCAT(' ', t2.speed) machine_speed,
       GROUP_CONCAT(' ', t3.twine) production_line
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id;

结果是：

| machine_number |  rpm   |   twine    |
----------------------------------------
|      MN-1      | 20, 20 | mono, poly |
|      MN-2      | 20, 25 | mono, poly |

我应该做什么？

Answer 1

看起来您的联接正在生成重复的行。

我们可以通过使用子查询来实现所需的输出。

SELECT t1.machine_number, t2.machine_speed, t3.production_line
FROM machine_list t1
LEFT JOIN (
  SELECT mc_recordID, GROUP_CONCAT(' ', speed) AS machine_speed
  FROM applicable_rpm
  GROUP BY mc_recordID
) t2 ON t1.id = t2.mc_recordID
LEFT JOIN (
  SELECT mc_recordID, GROUP_CONCAT(' ', twine) AS production_line
  FROM applicable_product
  GROUP BY mc_recordID
) t3 ON t1.id = t3.mc_recordID;

这将返回您预期的输出：

机器编号	机器速度	生产线
MN-1	20	单声道
MN-2	20, 25	单晶、多晶

Answer 2

如果您不分组，您将看到有两行与 MN-2 关联。因此，如果您进行 group_concat，它将显示两行中所选列的值。

SELECT *
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID;

您将需要在此处使用嵌套选择。类似于以下内容：

SELECT machine_number, 
    (SELECT GROUP_CONCAT(rpm) FROM applicable_rpm WHERE mc_recordID = t1.ID) as rpm,
    (SELECT GROUP_CONCAT(productline) FROM applicable_product WHERE mc_recordID = t1.ID) as twin,
    FROM machine_list t1;

事后想想，您也可以尝试使用 DISTINCT 进行 GROUP_CONCAT

SELECT t1.machine_id,
       GROUP_CONCAT(DISTINCT t2.speed) machine_speed,
       GROUP_CONCAT(DISTINCT t3.twine) production_line
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id;