我在連接三個表時遇到問題。 第一次嘗試連接兩個表是成功的，但在第三個表上結果不正確...
我有三個表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;