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 
  

查询是：

  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产品p 
 LEFT JOIN特价产品s ON p.products_id = s.products_id 
使用（manufacturer_id），
产品描述pd，
类别c，
产品类别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; 
  

解决方案

当您使用GROUP BY时，您的选择列表只有当他们有一个单一的价值每个组。在这种情况下，MySQL认为 s.status 可能每个值有多个值组。例如，按照 p.products_id 进行分组，但 s.status 是另一个表特价，可能与表产品具有一对多关系。因此，在特殊项目中可能有多行使用相同的 products_id ，但对于状态的不同值。如果是这种情况，查询使用的是 status 的哪个值？在您的数据中，您可能会碰巧限制这些行，使得在特殊项目中只有一行 $ b < >对于产品中的每一行。但MySQL无法做出这样的假设。

MySQL 5.6和更早的版本允许您编写这样的模糊查询，相信您知道自己在做什么。但是MySQL 5.7默认实现更严格的执行（这可以不像以前的版本那样严格执行）。

解决方法是遵循以下规则： select-list必须属于以下三种情况之一：

• 该列位于像COUNT（），SUM（），MIN ，MAX（），AVERAGE（）或GROUP_CONCAT（）。
  
• 该列是 GROUP BY 子句。

• 该列在功能上依赖于 GROUP BY 子句中指定的列。
>


有关更多解释，请阅读此优秀博客：揭穿GROUP BY神话

---

重新评论，我只能猜测，因为你还没有发布你的表定义。

我猜测 products_description 和制造商在功能上依赖于产品，因此可以将它们按原样列在选择列表中。但是这个假设可能并不正确，我不知道你的模式。

无论如何，关于 s.status 应该通过使用聚合函数来解决。我以 MAX（）为例。

  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产品p 
 LEFT OUTER JOIN特价商品ON p.products_id = s.products_id 
 INNER JOIN厂商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类别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 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屋！