我在连接三个表时遇到问题。 第一次尝试连接两个表是成功的，但在第三个表上结果不正确......
我有三个表machine_list是mainTable，然后applicable_rpm和applicable_product是machine_list的一些详细信息
表：machine_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 |
我应该做什么？
看起来您的联接正在生成重复的行。
我们可以通过使用子查询来实现所需的输出。
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-2 关联。因此，如果您进行 group_concat，它将显示两行中所选列的值。
您将需要在此处使用嵌套选择。类似于以下内容：
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;