我一直在迁移我的一些mySQL查询到postgresql使用Heroku …我的大多数查询工作正常，但我仍然有类似的定期错误，当我使用group by：
ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function
有人可以告诉我我做错了什么？
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
ActiveRecord::StatementInvalid: PGError: ERROR: column "availables.id" must appear in the GROUP BY clause or be used in an aggregate function : 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' AND E'2009-10-23') GROUP BY availables.bookdate ORDER BY availables.updated_at
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')
+-----+-------+-------+------------+---------+---------------+---------------+ | 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
MysqL的完全不符合标准的GROUP BY可以由Postgres的DISTINCT ON模拟。考虑这个 ：
原文链接：https://www.f2er.com/postgresql/193822.htmlSELECT a,b,c,d,e FROM table GROUP BY a
这为每个值(每一个，你不真正知道)的值提供1行。实际上你可以猜到，因为MysqL不知道哈希聚合，所以它可能会使用排序…但它只会排序，所以行的顺序可以是随机的。除非它使用多列索引而不是排序。嗯，反正，它不是由查询指定。
postgres：
SELECT DISTINCT ON (a) a,e FROM table ORDER BY a,c
这为a的每个值传递1行，根据查询指定的ORDER BY，此行将是排序中的第一行。简单。
注意这里，它不是我计算的聚合。所以GROUP BY实际上没有意义。 DISTINCT ON使得更有意义。
