mysql - SELECT 列表不在 GROUP BY 子句中并且包含非聚合列...与 sql_mode=only_full_group_by 不兼容

Question

我的数据看起来像这样资源表

id  name  weight  key
1   res1   12    1
2   res2   12    1
3   res3   13    2
4   res4   13    2

我有以下查询

SELECT
resource.id,
resource.name,
resource.weight,

from resource join 
inventory on resource.weight = inventory.weight
where inventory.key = ?
GROUP BY resource.weight;

我想获取此数据，我想按重量分组，所以我只有所有唯一的重量值

id  name  weight  key
1   res1   12    1
4   res4   13    2

但是我收到此错误

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'RESOURCE.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

所以当我将 id 添加到 Group by 时

GROUP BY resource.value, resource.name, resource.id;

它有效，但现在我不再获得唯一的权重值，它会添加所有行。

如何在不关闭 sql_mode=only_full_group by 的情况下实现此目的？

Answer 1

如果没有聚合函数，就不能使用 group by 来减少行数..

为此，您应该选择一个聚合标准，例如分钟

  select m.* from my_table  m 
  inner join ( 
  select min(id) min_id,  weight
  from my_table 
  group by weight
  ) t on t.min_id =  m.id 

并加入表的结果子查询