MySQL 查询具有不同和顺序通过显示错误 3065(HY000)

Question

我正在我的机器上使用 linux mint 并尝试执行查询。执行后显示错误

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'shelterl_local.animal.changed' which is not in SELECT list; this is incompatible with DISTINCT

我添加了行

[mysqld]
sql-mode=''

在/etc/mysql/my.cnf文件中并重新启动mysql。但仍然遇到同样的错误。我引用了很多解决方案，但没有一个对我有用。我的查询是

SELECT DISTINCT fs.etid AS etid FROM og_membership fs 
LEFT OUTER JOIN node animal ON animal.nid = fs.etid LEFT OUTER JOIN 
field_data_field_for_animal dfa ON dfa.field_for_animal_value = fs.etid 
LEFT OUTER JOIN node pastid ON pastid.nid = dfa.entity_id WHERE ( (fs.gid = 
464) OR 
(animal.nid IN   
(1196113,1211208,1218831,1243640,1254254,
1253603,1249890,1261729,1261282,1258378,1273745,1270760,
1279219,1276040,1276031,1275684,1288727,1289306,1300545,
1313770,1313761,1313755,1313746,1313330,1312388,1310673,
1309431,1315024,1333640,1328041,1323565,1327216,1330104,
1327786,1326810,1335812,1333094,1341309,1340358,1348088,
1351077,1351071,318214,1342698,1472755,1491527,1351652,1353488,
1507763,1342713)) )AND (fs.entity_type = 'node') 
AND (animal.type = 'animal') AND (animal.status = 1) AND (pastid.title LIKE 
'%A%') 
ORDER BY animal.changed DESC LIMIT 0,300;

是否可以永久删除此错误并执行查询？请帮忙

Answer 1

您的初始查询相当于以下内容:

SELECT field1 AS f1
FROM table t
--Joins and conditions
GROUP BY field1
ORDER BY field2 DESC LIMIT 0,300

这在逻辑上没有意义，因为结果集中 field1 的每个值可能有多个与其关联的 field2 值。您看到的错误是 MySQL 表示它无法弄清楚您想要做什么的方式。一种解决方法是对 field2 的聚合进行排序，例如请尝试以下操作:

SELECT field1 AS f1
FROM table t
--Joins and conditions
GROUP BY field1
ORDER BY MAX(field2) DESC   -- or AVG(field2), or MIN(field2), etc.
LIMIT 0,300