PostgreSQL - 必须出现在 GROUP BY 子句中或在聚合函数中使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18061285/
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
PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
提问by Hrishikesh Sardar
I am getting this error in the pg production mode, but its working fine in sqlite3 development mode.
我在 pg 生产模式下遇到这个错误，但它在 sqlite3 开发模式下工作正常。
ActiveRecord::StatementInvalid in ManagementController#index
PG::Error: ERROR: column "estates.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "estates".* FROM "estates" WHERE "estates"."Mgmt" = ...
^
: SELECT "estates".* FROM "estates" WHERE "estates"."Mgmt" = 'Mazzey' GROUP BY user_id
@myestate = Estate.where(:Mgmt => current_user.Company).group(:user_id).all
采纳答案by Hrishikesh Sardar
@myestate1 = Estate.where(:Mgmt => current_user.Company)
@myestate = @myestate1.select("DISTINCT(user_id)")
this is what I did.
这就是我所做的。
回答by Craig Ringer
If user_idis the PRIMARY KEYthen you need to upgrade PostgreSQL; newer versions will correctly handle grouping by the primary key.
如果user_id是PRIMARY KEY则需要升级 PostgreSQL；较新的版本将正确处理按主键分组。
If user_idis neither unique northe primary key for the 'estates' relation in question, then this query doesn't make much sense, since PostgreSQL has no way to know whichvalue to return for each column of estateswhere multiple rows share the same user_id. You must use an aggregate function that expresses what you want, like min, max, avg, string_agg, array_agg, etc or add the column(s) of interest to the GROUP BY.
如果user_id既不是唯一的也不是有问题的“遗产”关系的主键，然后这个查询请求也没有太大的意义，因为PostgreSQL有没有办法知道它返回的每个列的值estates在多个行共享相同的user_id。您必须使用聚合函数，表达你想要什么，喜欢min，max，avg，string_agg，array_agg，等或感兴趣的列（S）添加到GROUP BY。
Alternately you can rephrase the query to use DISTINCT ONand an ORDER BYif you really do want to pick a somewhat arbitrary row, though I really doubt it's possible to express that via ActiveRecord.
或者你可以改写查询中使用DISTINCT ON和ORDER BY如果你确实想挑一个有点乱排，虽然我真的怀疑它可能表达通过ActiveRecord的。
Some databases - including SQLite and MySQL - will just pick an arbitrary row. This is considered incorrect and unsafe by the PostgreSQL team, so PostgreSQL follows the SQL standard and considers such queries to be errors.
一些数据库 - 包括 SQLite 和 MySQL - 只会选择任意行。这被 PostgreSQL 团队认为是不正确和不安全的，因此 PostgreSQL 遵循 SQL 标准并将此类查询视为错误。
If you have:
如果你有：
col1 col2
fred 42
bob 9
fred 44
fred 99
and you do:
你也是：
SELECT col1, col2 FROM mytable GROUP BY col1;
then it's obvious that you should get the row:
那么很明显你应该得到这一行：
bob 9
but what about the result for fred? There is no single correct answer to pick, so the database will refuse to execute such unsafe queries. If you wanted the greatestcol2for any col1you'd use the maxaggregate:
但是结果fred呢？没有单一的正确答案可供选择，因此数据库将拒绝执行此类不安全的查询。如果你想要最大的col2，col1你可以使用max聚合：
SELECT col1, max(col2) AS max_col2 FROM mytable GROUP BY col1;
回答by Jin Hian Lee
I recently moved from MySQL to PostgreSQL and encountered the same issue. Just for reference, the best approach I've found is to use DISTINCT ON as suggested in this SO answer:
我最近从 MySQL 转移到 PostgreSQL 并遇到了同样的问题。仅供参考，我发现的最佳方法是按照此 SO 答案中的建议使用 DISTINCT ON：
Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord
Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord
This will let you get one record for each unique value in your chosen column that matches the other query conditions:
这将使您为所选列中与其他查询条件匹配的每个唯一值获取一条记录：
MyModel.where(:some_col => value).select("DISTINCT ON (unique_col) *")
I prefer DISTINCT ON because I can still get all the other column values in the row. DISTINCT alone will only return the value of that specific column.
我更喜欢 DISTINCT ON，因为我仍然可以获得行中的所有其他列值。单独的 DISTINCT 只会返回该特定列的值。
回答by hec
After often receiving the error myself I realised that Rails (I am using rails 4) automatically adds an 'order by id' at the end of your grouping query. This often results in the error above. So make sure you append your own .order(:group_by_column) at the end of your Rails query. Hence you will have something like this:
在我自己经常收到错误之后，我意识到 Rails（我使用的是 Rails 4）会在分组查询的末尾自动添加一个“按 ID 排序”。这通常会导致上述错误。因此，请确保在 Rails 查询的末尾附加您自己的 .order(:group_by_column) 。因此你会有这样的事情：
@problems = Problem.select('problems.username, sum(problems.weight) as weight_sum').group('problems.username').order('problems.username')
