在MYSQL中連接引用表1的3個表並用逗號分隔符號組合值

Question

我在連接三個表時遇到問題。 第一次嘗試連接兩個表是成功的，但在第三個表上結果不正確...

我有三個表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 |

我該做什麼？

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;