我一直在将一些MySQL查询迁移到PostgreSQL以使用Heroku。我的大多数查询工作正常,但是当我使用group by时,我仍然遇到类似的重复错误:
错误:“ XYZ”列必须出现在GROUP BY子句中或在聚合函数中使用
有人可以告诉我我在做什么错吗?
可以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错误:
ActiveRecord :: StatementInvalid:PG错误:错误:列“ availables.id”必须出现在GROUP BY子句中或在聚合函数中使用: SELECT“ availables”。* FROM“ availables” INNER JOIN“ rooms” ON“ rooms”。 id =“ availables” .room_id在哪里(rooms.hotel_id = 5056 AND availables.bookdate在E‘2009-10-21’和E‘2009-10-23’之间)GROUP BY availables.bookdate订单BY availables.updated_at
Ruby代码生成SQL:
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 | 价格| 点| 预定日期 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行
GROUP BYPostgres可以模拟完全不符合MySQL标准的MySQL DISTINCT ON。考虑一下:
GROUP BY
DISTINCT ON
SELECT a,b,c,d,e FROM table GROUP BY a
每个值传送1行a(您实际上并不知道该行)。好的,实际上您可以猜测,因为MySQL不了解哈希聚合,因此它可能会使用sort …,但只会排序a,因此行的顺序可以是随机的。除非它使用多列索引而不是排序。好吧,无论如何,它不是由查询指定的。
a
SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c
这样,每个值传送1行a,该行将是根据ORDER BY查询指定的排序中的第一行。简单。
ORDER BY
请注意,这里不是我正在计算的汇总。所以GROUP BY实际上没有任何意义。DISTINCT ON更有意义。
Rails已与MySQL结为夫妻,因此对于它生成的SQL在Postgres中不起作用,我并不感到惊讶。