Ruby-on-rails GroupingError: ERROR: 列必须出现在 GROUP BY 子句中或用于聚合函数中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20942477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
提问by Reuben
I have code in my controller that is ranking albums by the highest average review rating (used code from this solution How to display highest rated albums through a has_many reviews relationship):
我的控制器中有代码按最高平均评论评分对专辑进行排名（使用此解决方案中的代码如何通过 has_many 评论关系显示评分最高的专辑）：
@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC")
This code works perfectly in my development environment (sqlite3), however when I pushed the code to heroku and to postgresql I got this error:
这段代码在我的开发环境 (sqlite3) 中完美运行，但是当我将代码推送到 heroku 和 postgresql 时，我收到了这个错误：
PG::GroupingError: ERROR: column "reviews.id" must appear in the GROUP BY clause or be used in an aggregate function
I realize this is a fairly common problem, I am a bit inexperienced with SQL so I am having trouble refactoring the code so it will work in both my development and production environments.
我意识到这是一个相当普遍的问题，我对 SQL 有点缺乏经验，所以我在重构代码时遇到了麻烦，因此它可以在我的开发和生产环境中工作。
回答by slash
You are not allowed to select reviews.id(selected implicitly through the wildcard *) without adding it to the GROUP BYclause or applying an aggregate function like avg(). The solution is to do one of the following:
不允许选择reviews.id（通过通配符隐式选择*）而不将其添加到GROUP BY子句或应用诸如avg(). 解决方案是执行以下操作之一：
- Remove the wildcard
*from your select - Add the field
reviews.idto your group clause - Select
reviews.idexplicitly and apply an aggregate function to it (e.g.sum(reviews.id)) - Replace the wildcard
*with the table-specific wildcardalbums.*
*从您的选择中删除通配符- 将该字段添加
reviews.id到您的组子句中 reviews.id显式选择并对其应用聚合函数（例如sum(reviews.id)）- 将通配符替换为
*特定于表的通配符albums.*
The second and third option do not make much sense in your scenario though. Based on your comment, I added option four.
不过，第二个和第三个选项在您的场景中没有多大意义。根据您的评论，我添加了选项四。
回答by d1jhoni1b
Just would like to share this code on ruby using active record (sinatra)
只是想使用活动记录（sinatra）在 ruby 上共享此代码
I had to add "group by" to an "order by" function, so line of code ...
我不得不将“group by”添加到“order by”函数中，所以代码行......
from:
从：
@models = Port.all.order('number asc')
to:
到：
@models = Port.select(:id, :device_id, :number, :value, :sensor, :UOM).all.order('number asc').group(:id,:sensor,:UOM)
and it worked perfect, just remember the ID field in this case "Port.id" must be added to the group clause otherwise will raise this error, and as @slash mentioned you can not achieve this with special functions (select implicitly through the wildcard * or in my case using "all")
并且它工作得很好，只要记住在这种情况下的 ID 字段“Port.id”必须添加到组子句中，否则会引发此错误，并且正如@slash 提到的，您无法使用特殊功能实现此目的（通过通配符隐式选择* 或在我的情况下使用“全部”）
回答by MoskitoHero
The only really acceptable solution I found to this kind of problem was with this code:
我找到的解决此类问题的唯一真正可接受的解决方案是使用以下代码：
@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group_by(&:id).order("average_rating DESC")
I hope it helps someone.
我希望它可以帮助某人。
