MySQL Error: SELECT list is not in GROUP BY clause(MySQL 错误:SELECT 列表不在 GROUP BY 子句中)
问题描述
我的查询有问题,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 子句中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:MySQL 错误:SELECT 列表不在 GROUP BY 子句中
基础教程推荐
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01