MySQL 5.0.12 - 列表不在 GROUP BY 子句中并且包含非聚合列? [英] MySQL 5.0.12 - list is not in GROUP BY clause and contains nonaggregated column?

问题描述

为什么我在 mysqlnd 5.0.12-dev 上出现下面这个错误:

Why do I get this error below on mysqlnd 5.0.12-dev:

功能上依赖于 GROUP BY 子句中的列；这是与 sql_mode=only_full_group_by 不兼容

1055 - Expression #29 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_name.p2.url' which is not

functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

查询:

SELECT p.* , 
    p2.article_id AS parent_id  , 
    p2.url AS parent_url  , 
    p3.article_id AS parent_parent_id  , 
    p3.url AS parent_parent_url  , 
    p3.title AS parent_parent_title   

FROM article AS p  
LEFT JOIN article AS p2  
ON p2.article_id = p.parent_id  
AND p.article_id <> p2.article_id  

LEFT JOIN article AS p3  
ON p3.article_id = p2.parent_id  
AND p2.article_id <> p3.article_id  

WHERE p.url = 'contact'  
AND p.type = 'page'  
AND p.hide = '0'  
GROUP BY p.article_id  
ORDER BY p.backdated_on DESC 

查询在 mysqlnd 5.0.11-dev 上正常工作.

知道发生了什么吗?

推荐答案

错误很明显:

功能上依赖于 GROUP BY 子句中的列；这是与 sql_mode=only_full_group_by 不兼容

functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

默认情况下，MySQL 允许您拥有查询的结构.当您的服务器更新时，有人(明智地，恕我直言)设置了 SQL 模式，因此引擎将不支持此功能.

By default, MySQL allows the structure of the query as you have it. When your server was updated, someone (wisely, IMHO) set the SQL mode so the engine would not support this functionality.

不清楚你想做什么.但我猜 GROUP BY 甚至没有必要:

It is unclear what you want to do. But I'm guessing that the GROUP BY is not even necessary:

SELECT p.* , 
       p2.article_id AS parent_id  , 
       p2.url AS parent_url  , 
       p3.article_id AS parent_parent_id  , 
       p3.url AS parent_parent_url  , 
       p3.title AS parent_parent_title   
FROM article p LEFT JOIN
     article p2  
     ON p2.article_id = p.parent_id AND
     p.article_id <> p2.article_id LEFT JOIN
     article p3  
     ON p3.article_id = p2.parent_id AND
     p2.article_id <> p3.article_id  
WHERE p.url = 'contact' AND p.type = 'page' AND p.hide = '0'  
ORDER BY p.backdated_on DESC ;

如果不知何故，您得到重复项，那么您可能需要 SELECT DISTINCT.

If, somehow, you are getting duplicates, then you might want SELECT DISTINCT.

如果这仍然不能解决您的问题，请询问另一个问题(因为这个问题已经有多个解决问题中的语法问题的答案).提供示例数据和所需的结果，以及您开始工作的查询.

If that still doesn't solve your problem, ask another question (because this one already has multiple answers that address the syntax issue in the question). Provide sample data and desired results, as well as the query that you get to work.

这篇关于MySQL 5.0.12 - 列表不在 GROUP BY 子句中并且包含非聚合列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！