不同于 MySQL 的 PostgreSQL GROUP BY？

我一直在将我的一些 MySQL 查询迁移到 PostgreSQL 来使用 Heroku。我的大多数查询都能正常工作，但是当我使用 group by 时，总会出现类似的反复出现的错误:

ERROR: 列“ XYZ”必须出现在 GROUPBY 子句中，或者在 聚合函数

有人能告诉我哪里做错了吗？


100% 工作的 MySQL:

SELECT `availables`.*
FROM `availables`
INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
GROUP BY availables.bookdate
ORDER BY availables.updated_at


PostgreSQL 错误:

无效: PGError: ERROR: column 必须出现在 GROUPBY 子句中，或者在 总函数:
选择“可用” 在“ room”上加入“ room”. id = “ ailables”. room _ id WHERE (room s.hotel _ id = 5056 AND ailables.bookdate BETWEEN E’2009-10-21’) 和 E’2009-10-23’)组可用 Update _ at


生成 SQL 的 Ruby 代码:

expiration = Available.find(:all,
:joins => [ :room ],
:conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
:group => 'availables.bookdate',
:order => 'availables.updated_at')


预期输出(来自正常运行的 MySQL 查询) :

+-----+-------+-------+------------+---------+---------------+---------------+
| id  | price | spots | bookdate   | room_id | created_at    | updated_at    |
+-----+-------+-------+------------+---------+---------------+---------------+
| 414 | 38.0  | 1     | 2009-11-22 | 1762    | 2009-11-20... | 2009-11-20... |
| 415 | 38.0  | 1     | 2009-11-23 | 1762    | 2009-11-20... | 2009-11-20... |
| 416 | 38.0  | 2     | 2009-11-24 | 1762    | 2009-11-20... | 2009-11-20... |
+-----+-------+-------+------------+---------+---------------+---------------+
3 rows in set
35701 次浏览
小开

MySQL 的 GROUP BY 可以在没有聚合函数的情况下使用(这与 SQL 标准相反) ，并返回组中的第一行(我不知道基于什么标准) ，而 PostgreSQL 必须在发出 GROUP BY 子句的列上有一个聚合函数(MAX、 SUM 等)。

小开

如果我没记错的话，在 PostgreSQL 中，您必须添加从 GROUPBY 子句应用 到的表中获取的每一列 GROUPBY 子句。

小开

PostgreSQL 比 MySQL 更符合 SQL。输出中的所有字段(带聚合函数的计算字段除外)必须出现在 GROUPBY 子句中。

小开

正确，解决这个问题的方法是使用: 选择并选择您希望用来装饰结果对象的每个字段，并按它们进行分组。

令人讨厌的-但这是 应该分组的工作方式，与 MySQL 的工作方式相反，如果您不在 应该分组中添加字段，那么可以通过猜测来猜测您的意思。

小开
最佳答案

MySQL 的完全不符合标准的 GROUP BY可以被 Postgres 的 DISTINCT ON模仿:

MySQL:

SELECT a,b,c,d,e FROM table GROUP BY a

这样每个 a值传递1行(您并不真正知道是哪一行)。实际上你可以猜到，因为 MySQL 不知道散列聚合，所以它可能会使用排序... 但它只会在 a上排序，所以行的顺序可能是随机的。除非它使用多列索引而不是排序。不管怎样，查询没有指定它。

后记:

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

每个 a值传递1行，该行将是根据查询指定的 ORDER BY排序的第一行。很简单。

请注意，在这里，它不是我正在计算的聚合。所以 GROUP BY实际上毫无意义。DISTINCT ON更有意义。

Rails 与 mySQL 结合在一起，所以我对于它生成在 Postgres 不能工作的 SQL 并不感到惊讶。

小开

根据 MySQL 的“解密神话”http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html。SQL (标准的2003版本)不要求查询的 SELECT 列表中引用的列也出现在 GROUPBY 子句中。

小开

我认为. uniq [1]可以解决你的问题。

[1] Available.select('...').uniq

看看 http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

小开

虽然不是最好的解决方案，但是在 PostgreSQL 中可以将 group 参数改为输出 model 中的每一列:

expiration = Available.find(:all,
:joins => [ :room ],
:conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
:group => Available.column_names.collect{|col| "availables.#{col}"},
:order => 'availables.updated_at')
小开

对于其他寻找按任何字段(包括联接字段)在 postgreql 中排序的方法的人，使用子查询:

SELECT * FROM(
SELECT DISTINCT ON(availables.bookdate) `availables`.*
FROM `availables` INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5056
AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
) AS distinct_selected
ORDER BY availables.updated_at


or arel:


subquery = SomeRecord.select("distinct on(xx.id) xx.*, jointable.order_field")
.where("").joins(")
result = SomeRecord.select("*").from("(#{subquery.to_sql}) AS distinct_selected").order(" xx.order_field ASC, jointable.order_field ASC")

提交答案