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

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

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

问题

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

我有三个表 machine_list 是 mainTable，然后 applicable_rpm 和 applicable_product 是 machine_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. 看起来你的连接产生了重复行。
  3. 我们可以通过使用子查询来实现所需的输出。
  5. SELECT t1.machine_number, t2.machine_speed, t3.production_line
  6. FROM machine_list t1
  7. LEFT JOIN (
  8.   SELECT mc_recordID, GROUP_CONCAT(' ', speed) AS machine_speed
  9.   FROM applicable_rpm
  10.   GROUP BY mc_recordID
  11. ) t2 ON t1.id = t2.mc_recordID
  12. LEFT JOIN (
  13.   SELECT mc_recordID, GROUP_CONCAT(' ', twine) AS production_line
  14.   FROM applicable_product
  15.   GROUP BY mc_recordID
  16. ) t3 ON t1.id = t3.mc_recordID;
  18. 这将返回你所期望的输出：
  20. | 机器编号   | 机器速度   | 生产线       |
  21. |------------|------------|--------------|
  22. | MN-1       | 20         | 单体线       |
  23. | 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
  • mysql
  • sql
匿名

发表评论

匿名网友

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

确定