sql - PostgreSQL GROUP BY 与 MySQL 不同？

Question

我一直在将一些 MySQL 查询迁移到 PostgreSQL 以使用 Heroku。我的大部分查询都工作正常，但是当我使用 group by 时，我一直遇到类似的重复错误:

ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function

谁能告诉我我做错了什么？

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: 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

生成 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

Answer 1

MySQL 完全不符合标准的 GROUP BY 可以被 Postgres 的 DISTINCT ON 模拟。考虑一下:

MySQL:

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

这会为 a 的每个值提供 1 行(您实际上并不知道是哪一个)。实际上你可以猜到，因为 MySQL 不知道散列聚合，所以它可能会使用排序...但它只会对 a 进行排序，因此行的顺序可能是随机的.除非它使用多列索引而不是排序。嗯，反正不是查询指定的。

Postgres:

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 结合，所以我对它生成的 SQL 在 Postgres 中不起作用并不感到惊讶。