MySQL 错误:SELECT 列表不在 GROUP BY 子句中 [英] MySQL Error: SELECT list is not in GROUP BY clause

问题描述

我的查询有问题，mysql 抛出以下错误:

<块引用>

#1055 - SELECT 列表的表达式 #66 不在 GROUP BY 子句中，并且包含非聚合列s.status"，它在功能上不起作用依赖于 GROUP BY 子句中的列；这与sql_mode=only_full_group_by

查询是:

选择p.*,pd.*,米*，IF(s.status, s.specials_new_products_price, null) as specials_new_products_price,IF(s.status, s.specials_new_products_price, p.products_price) 作为 final_priceFROM 产品 pLEFT JOIN 特价 s ON p.products_id = s.products_idLEFT JOIN 制造商 m using(manufacturers_id) ,products_description pd,c类，products_to_categories p2c哪里 p.products_view = 1AND p.products_status = 1AND p.products_archive = 0AND c.virtual_categories = 0AND p.products_id = pd.products_idAND p.products_id = p2c.products_idAND p2c.categories_id = c.categories_idAND pd.language_id = 1按 p.products_id 分组；

解决方案

当您使用 GROUP BY 时，您可以在选择列表中使用表达式，前提是每个组只有一个值.否则，您会得到不明确的查询结果.

在您的情况下，MySQL 认为 s.status 每组可能有多个值.例如，您按 p.products_id 分组，但 s.status 是另一个表 specials 中的列，可能是一对- 与表 products 的许多关系.因此，specials 中可能有多行具有相同的 products_id，但 status 的值不同.如果是这种情况，查询应该使用 status 的哪个值?这是模棱两可的.

在您的数据中，您可能碰巧限制了行，以便 specials 中的每一行对于 products 中的每一行.但是 MySQL 不能做出这样的假设.

MySQL 5.6 及更早版本允许您编写如此模糊的查询，相信您知道自己在做什么.但 MySQL 5.7 默认启用更严格的强制执行(这可以变得不那么严格，使其表现得像早期版本一样).

解决方法是遵循以下规则:选择列表中的每一列都必须属于以下三种情况之一:

• 该列位于聚合函数内，例如 COUNT()、SUM()、MIN、MAX()、AVERAGE() 或 GROUP_CONCAT().
• 该列是 GROUP BY 子句中命名的列之一.
• 该列在功能上依赖于 GROUP BY 子句中命名的列.

欲了解更多解释，请阅读这篇优秀的博客:揭穿 GROUP BY 神话

<小时>

关于你的评论，我只能猜测，因为你还没有发布你的表定义.

我猜 products_description 和 manufacturers 在功能上依赖于 products，所以可以在选择中按原样列出它们 -列表.但是这个假设可能不正确，我不知道你的架构.

无论如何，s.status 的错误应该使用聚合函数来解决.我以 MAX() 为例.

SELECT p.*,pd.*,米*，MAX(IF(s.status, s.specials_new_products_price, NULL))AS specials_new_products_price,MAX(IF(s.status, s.specials_new_products_price, p.products_price))AS final_priceFROM 产品 pLEFT OUTER JOIN 特价 s ON p.products_id = s.products_idINNER JOIN 制造商 m ON p.manufacturers_id = m.manufacturers_idINNER JOIN products_description pd ON p.products_id = pd.products_idINNER JOIN products_to_categories p2c ON p.products_id = p2c.products_idINNER JOIN 类别 c ON p2c.categories_id = c.categories_id哪里 p.products_view = 1AND p.products_status = 1AND p.products_archive = 0AND c.virtual_categories = 0AND pd.language_id = 1按 p.products_id 分组；

我还以正确的方式重写了您的连接.应避免逗号样式的连接.

I have a problem with my query and mysql throws the following error:

#1055 - Expression #66 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 's.status' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

Query is:

select   p.*,
pd.*,
m.*,
IF(s.status, s.specials_new_products_price, null) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price
FROM products p 
LEFT JOIN specials s ON p.products_id = s.products_id  
LEFT JOIN manufacturers m using(manufacturers_id) , 
          products_description pd,
          categories c,
          products_to_categories p2c
WHERE p.products_view = 1  
AND p.products_status = 1
AND p.products_archive = 0
AND c.virtual_categories = 0
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND pd.language_id = 1

GROUP BY p.products_id;

解决方案

When you use GROUP BY, you can use expressions in your select-list only if they have a single value per group. Otherwise you get ambiguous query results.

In your case, MySQL believes that s.status might have multiple values per group. For example, you're grouping by p.products_id but s.status is a column in another table specials, perhaps in a one-to-many relationship with table products. So there might be multiple rows in specials with the same products_id, but different values for status. If that's the case, which value for status should the query use? It's ambiguous.

In your data, you might happen to limit the rows such that you only have one row in specials for each row in products. But MySQL can't make that assumption.

MySQL 5.6 and earlier let you write such ambiguous queries, trusting that you know what you're doing. But MySQL 5.7 enables more strict enforcement by default (this can be made less strict to behave like earlier versions).

The fix is to follow this rule: Every column in your select-list must fall into one of three cases:

• The column is inside an aggregate function like COUNT(), SUM(), MIN, MAX(), AVERAGE(), or GROUP_CONCAT().
• The column is one of the column(s) named in the GROUP BY clause.
• The column is functionally dependent on the column(s) named in the GROUP BY clause.

For more explanation read this excellent blog: Debunking GROUP BY myths

---

Re your comment, I can only make a guess because you have not posted your table definitions.

I'm guessing that products_description and manufacturers are functionally dependent on products, so it's okay to list them as is in the select-list. But this assumption may not be correct, I don't know your schema.

Anyway, the error about s.status should be resolved by using an aggregate function. I'm using MAX() as an example.

SELECT p.*,
pd.*,
m.*,
MAX(IF(s.status, s.specials_new_products_price, NULL)) 
  AS specials_new_products_price,
MAX(IF(s.status, s.specials_new_products_price, p.products_price)) 
  AS final_price
FROM products p 
LEFT OUTER JOIN specials s ON p.products_id = s.products_id  
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
INNER JOIN products_description pd ON p.products_id = pd.products_id
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
INNER JOIN categories c ON p2c.categories_id = c.categories_id
WHERE p.products_view = 1  
AND p.products_status = 1
AND p.products_archive = 0
AND c.virtual_categories = 0
AND pd.language_id = 1
GROUP BY p.products_id;

I also rewrote your joins in the proper way. Comma-style joins should be avoided.

这篇关于MySQL 错误:SELECT 列表不在 GROUP BY 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！