PostgreSQL GROUP BY与MySQL不同？

Question

我一直在将一些MySQL查询迁移到PostgreSQL以使用Heroku。我的大多数查询工作正常，但当我使用group by时，我仍然遇到类似的重复错误：

错误：列“XYZ”必须出现在GROUP BY子句中或用于聚合函数

有人能告诉我我做错了什么吗？

MySQL 100％工作：

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错误：

ActiveRecord :: StatementInvalid：PGError：错误：列“availables.id”必须出现在GROUP BY子句中或用于聚合函数：

SELECT“availables”。* FROM“availables”INNER JOIN“rooms”ON“rooms”。 id =“availables”.room_id WHERE（rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21'EN'E'2009-10-23'）GROUP BY availables.bookdate ORDER BY availables.updated_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 | 价格| 斑点| 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行

Answer 1

MySQL的完全非标准兼容性GROUP BY可以由Postgres模仿DISTINCT ON。考虑一下：MySQL的：SELECT a,b,c,d,e FROM table GROUP BY a每个值提供1行a（哪一个，你真的不知道）。实际上你可以猜到，因为MySQL不知道哈希聚合，所以它可能会使用排序......但它只会排序a，所以行的顺序可能是随机的。除非它使用多列索引而不是排序。好吧，无论如何，它没有被查询指定。Postgres的：SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c这样每个值传送1行a，该行将根据ORDER BY查询指定的排序中的第一行。简单。请注意，这里，它不是我正在计算的聚合。所以GROUP BY实际上毫无意义。DISTINCT ON更有意义。Rails与MySQL结合，所以我并不惊讶它生成的SQL在Postgres中不起作用。

Answer 2

PostgreSQL比MySQL更符合SQL。输出中的所有字段（除了具有聚合函数的计算字段）必须存在于GROUP BY子句中。

Answer 3

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