為什麼這個連接提供多重價值？

這是我的查詢：

SELECT GROUP_CONCAT(c.categoryName) AS categories
FROM post p
        INNER JOIN join_post_cat jpc USING (postId)
        INNER JOIN categories c USING (categoryId)
WHERE p.postId = 1;

這是輸出：

+-----------------------------------------------------+
| categories                                          |
+-----------------------------------------------------+
| Apartment/Flat,Auditorium,Bachelor,Community Center |
+-----------------------------------------------------+
1 row in set (0.00 sec)

這是另一個查詢：

SELECT GROUP_CONCAT(a.areaName) AS locations
FROM join_area ja
        INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE ja.areaId = 29;

這是輸出：

+----------------------------------------------------------------+
| locations                                                      |
+----------------------------------------------------------------+
| Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

這兩個查詢數據通過ONE TO MANY關係相互關聯。因此，當我在一個查詢中同時處理兩個查詢時，這些輸出會重複。

這是更新的查詢：

SELECT GROUP_CONCAT(c.categoryName) AS categories,
      GROUP_CONCAT(a.areaName)     AS locations
FROM post p
        INNER JOIN join_post_cat jpc USING (postId)
        INNER JOIN categories c USING (categoryId)
        INNER JOIN join_area ja USING (areaId)
        INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE p.postId = 1;

這是輸出：

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| categories                                                                                                                                                                                                                                                          | locations                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Apartment/Flat,Apartment/Flat,Apartment/Flat,Apartment/Flat,Apartment/Flat,Auditorium,Auditorium,Auditorium,Auditorium,Auditorium,Bachelor,Bachelor,Bachelor,Bachelor,Bachelor,Community Center,Community Center,Community Center,Community Center,Community Center | Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

當我在distinct裡面添加時，GROUP_CONCAT我得到了所需的輸出。

這是輸出正確的查詢：

SELECT GROUP_CONCAT(distinct c.categoryName) AS categories,
      GROUP_CONCAT(distinct a.areaName)     AS locations
FROM post p
        INNER JOIN join_post_cat jpc USING (postId)
        INNER JOIN categories c USING (categoryId)
        INNER JOIN join_area ja USING (areaId)
        INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE p.postId = 1;

輸出：

+-----------------------------------------------------+----------------------------------------------------------------+
| categories                                          | locations                                                      |
+-----------------------------------------------------+----------------------------------------------------------------+
| Apartment/Flat,Auditorium,Bachelor,Community Center | Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+-----------------------------------------------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

現在我想知道：

• 為什麼 MySQL 多次獲取相同的數據？
• 為什麼我需要添加distinct？（我知道要刪除重複數據，但為什麼 MySQL 會多次獲取這些數據）
• 有沒有更好的方法可以在不獲取重複數據或不使用distinctor的情況下完成此任務group？

那些表格圖：

這樣想吧……

步驟 1. 建構JOINs.

步驟 2. 執行GROUP BY.

該怎麼做？

是的，你可以做一個DISTINCT. 但這只會隱藏查詢做太多工作的問題。

相反，分開GROUP BYs：

SELECT ( SELECT GROUP_CONCAT(c.categoryName)
             FROM categories WHERE c.categoryId = p.categoryId
      ) AS categories,
      ( SELECT GROUP_CONCAT(a.areaName)
             FROM join_area WHERE ja.areaId = p.areaId  -- (or whatever)
      )      AS locations
FROM post p
WHERE p.postId = 1;

好的，有子查詢會花費一些東西，但至少你得到了去重的答案，而不需要額外的去重。

請務必遵循 many:many 表上的提示：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

引用自：https://dba.stackexchange.com/questions/242211