选择SELECT中的多列时，使用GROUP BY和ORDER BY进行查询不起作用 [英] Query with GROUP BY and ORDER BY not working when multiple columns in SELECT are chosen

问题描述

我正在更新一个旧网站，但其中一个查询不再起作用:

I'm updating an old website and one of the queries isn't working anymore:

SELECT * FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

我注意到，如果我放下了GROUP BY，它会起作用，但是结果集与原始集不匹配:

I noticed if I dropped the GROUP BY it works, but the result set doesn't match the original:

SELECT * FROM tbl WHERE col1 IS NULL ORDER BY col2

所以我尝试在

So I tried reading up on GROUP BY in the docs to see what might be the issue, and it seemed to suggest not using * to select all the fields, but explicitly using the column name so I tried it with just the column that was being ordered and grouped:

SELECT col2 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

这是可行的，但是在浏览了代码之后，查询需要查询中的2列，因此无论添加了*的人都覆盖了它，但是如果我添加该列会产生错误，类似地，添加第三列也会产生相同的错误:

Which works but after looking through the code the query requires 2 columns in the query so whoever added * was overdoing it, but if I add that column produces an error, similarly adding a third column produces the same error:

SELECT col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2
SELECT col1, col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

谁能告诉我为什么最后一个查询不起作用?我无法从 docs ，但这是获取我需要的结果集所需的最少查询.

Can anyone tell me why this last query doesn't work? I can't decipher why from the docs, but this is the minimum query required to get the result set I need.

在Adminer中运行查询时出现此错误

Running the query in Adminer I get this error

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'name.table.column' 
which is not functionally dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by

推荐答案

首先，当query()返回false时，您应该找出错误所在.您似乎正在使用PDO，所以我将把您定向到此页面: http://php.net/manual/en/pdo.error-handling.php

First of all, when query() returns false, you should find out what the error was. You seem to be using PDO, so I will direct you to this page: http://php.net/manual/en/pdo.error-handling.php

TL; DR-您应该启用PDO异常，否则您需要编写代码来检查对query()，prepare()和execute()的每次调用的结果，以查看是否发生了错误.如果是这样，请使用errorInfo()找出实际错误.做其他任何事情都是盲目的！

TL;DR - you should enable PDO exceptions, or else you need to write code to check the result of every call to query(), prepare(), and execute() to see if an error occurred. And if so, use errorInfo() to find out the actual error. Doing anything else is flying blind!

查询(1055)中的错误:SELECT列表的表达式#2不在GROUP BY中 子句，包含非聚合列'webvictoria.cats_oct.matchLink' 在功能上不依赖于GROUP BY子句中的列；这是 与sql_mode = only_full_group_by

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'webvictoria.cats_oct.matchLink' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这是一个常见问题.查看数十个标记为 mysql-错误1055 .

This is a common issue. See dozens of questions tagged mysql-error-1055.

我想您刚刚升级到MySQL 5.7.默认情况下，MySQL 5.7启用了严格模式，所以我想您刚刚升级了.在MySQL 5.6之前，严格模式是可选的，默认情况下未启用.

I guess you just upgraded to MySQL 5.7. MySQL 5.7 enabled strict mode by default, so I guess you just upgraded. Prior to MySQL 5.6, strict mode was optional and not enabled by default.

请参阅: https://dev.mysql .com/doc/refman/5.7/en/group-by-handling.html

您不能编写不明确的查询.如果您GROUP BY col2，应在col1和col3中使用每个组的行组中的哪个值?模棱两可.

You can't write ambiguous queries. If you GROUP BY col2, which value in the group of rows of each group should be used for col1 and col3? It's ambiguous.

在没有严格模式的情况下，MySQL从组中选择一个任意行.使用严格模式，它将恢复为标准SQL行为，并禁止模棱两可的查询.顺便说一下，这就是大多数其他品牌的SQL数据库的行为.

Without strict mode, MySQL chooses an arbitrary row from the group. With strict mode, it reverts to standard SQL behavior, and disallows the ambiguous query. This is how most other brands of SQL database behave, by the way.

要解决此问题，必须遵循以下规则:选择列表中的每一列都必须是以下其中一项:

To fix it, you must follow this rule: Every column in your select list must be one of:

• GROUP BY子句中的一列
• 一列在功能上取决于GROUP BY子句中的列(因此只能有一个值)
• 用于MIN()，MAX()，COUNT()，SUM()，AVG()或GROUP_CONCAT()之类的聚合函数中

有人为了使代码重新工作"而选择在MySQL 5.7中禁用严格模式.但是它无效起作用-它只是给出模棱两可的结果，就像在MySQL 5.7之前一样.

Some people choose to disable strict mode in MySQL 5.7 for the sake of "getting the code working again." But it isn't working—it's just giving ambiguous results like it did before MySQL 5.7.

最好修正查询的逻辑.

这篇关于选择SELECT中的多列时，使用GROUP BY和ORDER BY进行查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！