MySQL 与 sql_mode=only_full_group_by 不兼容

Question

我有以下查询(更新见下文(：

SELECT 
i0_.id AS id_0, 
i0_.address AS address_1, 
i1_.name AS name_2, 
c2_.name AS name_3, 
c3_.code AS code_4, 
l4_.iso AS iso_5, 
c5_.id AS id_6, 
c6_.name AS name_7, 
i7_.identifier AS identifier_8 
FROM institutions i0_ 
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id) 
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id) 
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id) 
LEFT JOIN country_spoken_languages c8_ ON (c8_.country_id = c3_.id) 
LEFT JOIN cities c5_ ON (c5_.id = i0_.city_id) 
LEFT JOIN city_languages c6_ ON (c6_.city_id = c5_.id) 
LEFT JOIN languages l4_ ON (l4_.id = i1_.language_id) 
LEFT JOIN institution_types i7_ ON (i0_.institution_type_id = i7_.id) 
WHERE c8_.is_primary = 1 
AND c8_.language_id = l4_.id 
AND c2_.language_id = 546 
AND i7_.identifier = "work_place" 
GROUP BY id_6 #here is the issue...

更新的查询

SELECT 
i0_.id AS id_0, 
i0_.address AS address_1, 
i1_.name AS name_2, 
c2_.name AS name_3, 
c3_.code AS code_4, 
c4_.name AS name_5, 
i5_.identifier AS identifier_6 
FROM institutions i0_ 
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id) 
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id) 
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id AND c2_.language_id = ?) 
LEFT JOIN country_spoken_languages c6_ ON (c6_.country_id = c3_.id AND c6_.language_id = ? AND c6_.is_primary = ?) 
LEFT JOIN city_languages c4_ ON (c4_.city_id = i0_.city_id) 
LEFT JOIN institution_types i5_ ON (i0_.institution_type_id = i5_.id) 
WHERE i5_.identifier = ? 
GROUP BY i0_.city_id

此查询存在GROUP_BY问题，我不确定如何解决：

1055 - SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列 'database.i0_.id'

不是

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

我知道这可以通过启动only_full_group_by轻松解决，但是我可以对我的查询做些什么来使其正常工作，而不必修改服务器上的MySQL设置？

Answer 1

如果你不想更改sql_mode=only_full_group_by

您可以简单地将聚合函数添加到未参与分组依据的列中(例如 min(( 或 max((

(在以前的版本中，此列的结果是不可预测的。 通过这种方式，您可以为获取这些列的值分配一个规则(

SELECT 
  i0_.id AS id_0, 
  min(i0_.address AS) address_1, 
  min(i1_.name) AS name_2, 
  min(c2_.name )AS name_3, 
  min(c3_.code) AS code_4, 
  min(c4_.name) AS name_5, 
  min(i5_.identifier) AS identifier_6 
FROM institutions i0_ 
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id) 
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id) 
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id AND c2_.language_id = ?) 
LEFT JOIN country_spoken_languages c6_ ON (c6_.country_id = c3_.id AND c6_.language_id = ? AND c6_.is_primary = ?) 
LEFT JOIN city_languages c4_ ON (c4_.city_id = i0_.city_id) 
LEFT JOIN institution_types i5_ ON (i0_.institution_type_id = i5_.id) 
WHERE i5_.identifier = ? 
GROUP BY i0_.city_id

Answer 2

我认为您应该至少在选择字段中使用聚合函数才能使查询正常工作，请参见下文 我尝试使用来自表"客户"和"订单"的示例数据库"经典模型"：

select c.customerNumber, sum(o.orderNumber)
from customers c
join orders o
on(c.customerNumber = o.customerNumber)
group by c.customerNumber;

返回结果，但如果我删除函数"sum(("，则输出为：

错误 1055 (42000(： SELECT list 的表达式 #2 不在 GROUP BY 子句中，并且包含非聚合列 'classicmodels.o.orderNumber'，该列在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=only_full_group_by 不兼容