刪除 ONLY_FULL_GROUP_BY 的替代方法

從 MySQL 5.5 升級到 MySQL 5.7 後，我的一些查詢出現錯誤：

錯誤 1055 (42000)：

SELECT 列表的表達式 #1 不在 GROUP BY 子句中，並且包含非聚合列“grocery.Product_Category.category_id”，它在功能上不依賴於 GROUP BY 子句中的列；這與 sql_mode=only_full_group_by 不兼容

我做了我的研究，找到了問題的原因以及如何解決它，基本上我只需要從@@sql_mode 中刪除 ONLY_FULL_GROUP_BY ，一切都會恢復正常。

但是，我想知道這是否是正確的做法。是否有替代方法，也許是建構查詢的更好方法？

這是我的情況（http://sqlfiddle.com/#!9/6f1bd）：

我有兩個表（我在這裡簡化了它們的結構但基本相同）：產品和類別以及多對多關係表，以允許產品屬於多個類別：

SELECT * FROM Product;

+------------+---------+
| product_id | name    |
+------------+---------+
|          1 | Tomato  |
|          2 | Orange  |
|          3 | Banana  |
|          4 | Lettuce |
|          5 | Carrot  |
+------------+---------+
5 rows in set (0,00 sec)

SELECT * FROM Category;

+-------------+------------+
| category_id | name       |
+-------------+------------+
|           1 | Fruits     |
|           2 | Vegetables |
+-------------+------------+
2 rows in set (0,00 sec)

我想從這兩個類別中獲取產品，所以最簡單的查詢是：

SELECT * FROM Product JOIN Product_Category USING(product_id)
 JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec

但是，如果一個產品存在於兩個類別中，我只想要一次，做 DISTINCT 選擇將無濟於事，因為 category_id 不同：

SELECT DISTINCT * FROM Product JOIN Product_Category USING(product_id) JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec)

因此，對於 MySQL 5.5，我在 product_id 欄位上使用了 GROUP BY 子句：

SELECT * FROM Product JOIN Product_Category USING(product_id)
 JOIN Category USING(category_id) GROUP BY product_id;

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
5 rows in set (0,00 sec)

有效地刪除了重複項，我知道結果不是確定性的，但我不在乎番茄是否出現在水果或蔬菜類別中，我只關心在結果集中只得到一次。

但是這個使用 MySQL 5.7 的查詢會導致上面提到的錯誤。

所以，我的問題是：是否有另一種（也許更好）方法來獲得相同的結果，而不必從@@sql_mode 中刪除 ONLY_FULL_GROUP_BY？

我建議以最小化放入 GROUP BY 所需的列數的方式重寫查詢。在您的情況下，您可以通過Product_Category僅將分組應用於表來做到這一點。

根據您的範例，該表具有以下條目：

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          1 |           2 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

由於您希望產品名稱在輸出中是唯一的，請將此表分組product_id，並category_id選擇例如每個產品的最小值：

SELECT
 product_id,
 MIN(category_id) AS category_id
FROM
 Product_Category
GROUP BY
 product_id

這將為您提供如下輸出：

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

您可以看到每個產品只列出一次。將該表連接到其他兩個表不會產生重複項。因此，只需將上述查詢作為派生表替換為Product_Category您的查詢（當然，也從中刪除您的 GROUP BY）：

SELECT
 *
FROM
 Product
 JOIN (
   SELECT
     product_id,
     MIN(category_id) AS category_id
   FROM
     Product_Category
   GROUP BY
     product_id
 ) AS pc USING(product_id)
 JOIN Category USING(category_id)
;

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