首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >子查询不按预期工作,尝试按组排序,MySQL 5.5vs5.7

问子查询不按预期工作,尝试按组排序,MySQL 5.5vs5.7
EN

Database Administration用户
提问于 2019-02-20 18:11:14
回答 1查看 726关注 0票数 2

我不知道/理解DB的和我想要的一样。我正在尝试ORDER BY,然后是GROUP BY,以便从数据库中的每一组结果中获得第一条记录。

代码语言:javascript
复制
-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)

CREATE TABLE IF NOT EXISTS `docs` (
  `field1` int(6) unsigned NOT NULL,
  `field2` int(6) unsigned NOT NULL,
  `field3` int(6) NOT NULL,
  `field4` int(6) NOT NULL,
  PRIMARY KEY (`field1`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES
  ('27908', '82', '1', '17'),
  ('27907', '82', '1', '50'),
  ('402', '25', '1', '90'),
  ('312', '25', '10', '8');

如果我ORDER BY,结果是预期的。注意,它不需要子查询即可工作。我只是打破了我最初的疑问:

代码语言:javascript
复制
SELECT * FROM 
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
)

结果(用表生成器形成):

代码语言:javascript
复制
+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|    312 |     25 |     10 |      8 |
|  27908 |     82 |      1 |     17 |
|  27907 |     82 |      1 |     50 |
+--------+--------+--------+--------+

ORDER BY然后GROUP BY按照MySQL v5.5中的预期工作:

代码语言:javascript
复制
SELECT * FROM 
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
) temp
GROUP BY field2

结果:

代码语言:javascript
复制
+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|  27908 |     82 |      1 |     17 |
+--------+--------+--------+--------+

但是它在v5.7中不再起作用,并导致错误:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我不明白如何在使用子查询或另一种方式编写在v5.7中工作的查询时修复。如何将此查询更改为在MySQL v5.7中工作?

EN

回答 1

Database Administration用户

回答已采纳

发布于 2019-02-20 19:08:33

您是对的,这在早期版本中是不同的。

...However,在版本5.7之前的MySQL中,默认行为更加宽容,因为它不会抱怨,然后任意选择任何值!它还有一个ANY_VALUE()函数,如果您确实需要和以前一样的行为,它可以作为解决这个问题的另一个解决方案.

来源

但这一变化是一件好事,因为错误过去是被压制的。我建议更改查询以获得所需的结果。我相信你想要的是field1是最大的。

Field1是主键,因此您可以执行以下操作

代码语言:javascript
复制
SELECT docs.field1,docs.field2,docs.field3,docs.field4
FROM 
(
SELECT max(field1) as field1 ,field2 FROM docs
GROUP BY field2
) temp
INNER JOIN docs
on temp.field1 = docs.field1 
ORDER BY field2, field1 desc;

DBFiddle

相反,要么禁用该功能,要么使用ANY_VALUE(),但我建议您不要这样做。

ANY_VALUE()可以给出一个不一致的结果集,一个例子可以是数字50,而不是预期的17作为field4,其中field1列= 27908。

DBFiddle

票数 1
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/230306

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档