在MySQL中连接3个表,引用表1,并使用逗号分隔符组合值。

huangapple go评论92阅读模式
英文:

Joining 3 tables referencing table 1 and combining values with comma delimiter in MYSQL

问题

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

我有三个表 machine_listmainTable,然后 applicable_rpmapplicable_productmachine_list 的一些细节

表:machine_list

  1. | id | machine_number | machine_brand |
  2. ---------------------------------------
  3. | 1 | MN-1 | TOYO |
  4. | 2 | MN-2 | AMITA |

表:applicable_rpm

  1. | id | mc_recordID | rpm |
  2. --------------------------
  3. | 1 | 1 | 20 |
  4. | 2 | 2 | 20 |
  5. | 3 | 2 | 25 |

表:applicable_product

  1. | id | mc_recordID | productline|
  2. ---------------------------------
  3. | 1 | 1 | mono |
  4. | 2 | 2 | mono |
  5. | 3 | 2 | poly |

我想要返回这样的结果:

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

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

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

结果是正确的:

  1. | machine_number | rpm |
  2. ---------------------------
  3. | MN-1 | 20 |
  4. | MN-2 | 20, 25 |

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

这是我的查询:

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

结果是:

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

我该怎么办?

英文:

I'm having trouble on joining three tables.
First attempt on joining two tables is success, but on third table the results are not correct...

I have three tables machine_list is mainTable then applicable_rpm and applicable_product are some details of machine_list

Table: machine_list

  1. | id | machine_number | machine_brand |
  2. ---------------------------------------
  3. | 1 | MN-1 | TOYO |
  4. | 2 | MN-2 | AMITA |

Table: applicable_rpm

  1. | id | mc_recordID | rpm |
  2. --------------------------
  3. | 1 | 1 | 20 |
  4. | 2 | 2 | 20 |
  5. | 3 | 2 | 25 |

Table: applicable_product

  1. | id | mc_recordID | productline|
  2. ---------------------------------
  3. | 1 | 1 | mono |
  4. | 2 | 2 | mono |
  5. | 3 | 2 | poly |

I want to return like this:

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

I first try joining the two table with this query:

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

and the result are:

  1. | machine_number | rpm |
  2. ---------------------------
  3. | MN-1 | 20 |
  4. | MN-2 | 20, 25 |

which is correct, but when i try on third table it duplicates it's value.

this my query:

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

and the result are:

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

What should i do?

答案1

得分: 2

如果你不进行分组,你会看到与MN-2相关联的两行。因此,如果你使用group_concat,它会显示来自两行的所选列的值。

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

在这里你需要使用嵌套查询。类似以下方式:

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

事后你也可以尝试使用带有DISTINCT的GROUP_CONCAT:

  1. SELECT t1.machine_id,
  2. GROUP_CONCAT(DISTINCT t2.speed) machine_speed,
  3. GROUP_CONCAT(DISTINCT t3.twine) production_line
  4. FROM machine_list t1
  5. INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
  6. INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
  7. GROUP BY t1.id;
英文:

If you don't group, you'll see there are two rows associated with MN-2. So if you group_concat it'll display the value for the selected column from both rows.

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

You're going to need to use nested selects here. Something like the following:

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

As an after thought you could also try GROUP_CONCAT with DISTINCT

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

答案2

得分: 1

好的,以下是翻译好的内容:

  1. 看起来你的连接产生了重复行。
  2. 我们可以通过使用子查询来实现所需的输出。
  3. SELECT t1.machine_number, t2.machine_speed, t3.production_line
  4. FROM machine_list t1
  5. LEFT JOIN (
  6. SELECT mc_recordID, GROUP_CONCAT(' ', speed) AS machine_speed
  7. FROM applicable_rpm
  8. GROUP BY mc_recordID
  9. ) t2 ON t1.id = t2.mc_recordID
  10. LEFT JOIN (
  11. SELECT mc_recordID, GROUP_CONCAT(' ', twine) AS production_line
  12. FROM applicable_product
  13. GROUP BY mc_recordID
  14. ) t3 ON t1.id = t3.mc_recordID;
  15. 这将返回你所期望的输出:
  16. | 机器编号 | 机器速度 | 生产线 |
  17. |------------|------------|--------------|
  18. | MN-1 | 20 | 单体线 |
  19. | MN-2 | 20, 25 | 单体线, 多体线 |
英文:

Looks like your join is producing duplicate rows.

We can achieve the desired output by making use of subqueries.

  1. SELECT t1.machine_number, t2.machine_speed, t3.production_line
  2. FROM machine_list t1
  3. LEFT JOIN (
  4. SELECT mc_recordID, GROUP_CONCAT(' ', speed) AS machine_speed
  5. FROM applicable_rpm
  6. GROUP BY mc_recordID
  7. ) t2 ON t1.id = t2.mc_recordID
  8. LEFT JOIN (
  9. SELECT mc_recordID, GROUP_CONCAT(' ', twine) AS production_line
  10. FROM applicable_product
  11. GROUP BY mc_recordID
  12. ) t3 ON t1.id = t3.mc_recordID;

This will return you the expected output :

machine_number machine_speed production_line
MN-1 20 mono
MN-2 20, 25 mono, poly

huangapple
  • 本文由 发表于 2023年2月16日 16:51:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/75469776.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定