错误 1055 (42000):SELECT 列表的表达式 #1 不在 GROUP BY 子句中......这与 sql_mode=only_full_group_by 不兼容 [英] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause ... this is incompatible with sql_mode=only_full_group_by
问题描述
我使用的是 MYSQL 5.7.我想获取具有不同 device_id
的最近行.我试过这些查询:
I am using MYSQL 5.7. I want to get the recent rows with distinct device_id
. I tried these queries:
查询 1
SELECT `table`.`id`, `table`.`device_id` FROM `table` WHERE (id IN (SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id) and device_id <> '');
+----+------------------+
| id | device_id |
+----+------------------+
| 5 | ffcecafe5eed4fba |
| 6 | ffcecafe5eed4fba |
| 8 | 71085f00e527bae0 |
+----+------------------+
3 rows in set (0.00 sec)
但它不会删除重复项.
子查询 1
SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'last_modified.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这给了错误.然后我在 MySQL 网站上发现使用 ANY_VALUE() 来消除这个错误.
This gave error. Then I found on MySQL website to use ANY_VALUE() to remove this error.
子查询 2
SELECT ANY_VALUE(id) FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id;
+---------------+------------------+
| ANY_VALUE(id) | device_id |
+---------------+------------------+
| 7 | |
| 8 | 71085f00e527bae0 |
| 5 | ffcecafe5eed4fba |
+---------------+------------------+
3 rows in set (0.00 sec)
这是给出不同的 ID.但是当我在上面的查询 1 中使用 ANY_VALUE 时,它给出了相同的结果.
This is giving distinct ids. But when I am using ANY_VALUE in query 1 above, its giving the same result.
如何在 MySQL 5.7 中查询不同的最近行?
How to query distinct recent rows in MySQL 5.7 ?
可能重复
推荐答案
这是对我有用的查询-
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;
感谢@Shadow 提供指向可能重复的链接.
Thanks @Shadow for the link to the possible duplicate.
这篇关于错误 1055 (42000):SELECT 列表的表达式 #1 不在 GROUP BY 子句中......这与 sql_mode=only_full_group_by 不兼容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!