mysql - 将 MySQL "get top y for each n"GROUP BY 查询转换为 PostgreSQL

Question

好的，因为 Heroku，我将从 mysql 转到 postgresql。我有这个在 mysql 中完美运行的声明，但我似乎无法找出 PG 的最佳方式/任何方式。

m = C.select("m.derp, c.id").joins("join m on m.c_id = c.id").where("m.id IN (SELECT max(id) from m group by c_id order by updated_at desc)").order("m.updated_at desc")

所以基本上我需要抓取按 c 分组的 m，按最新创建的 m 排序。 Postgres 不会让我选择多个列，除非它们通过调用在组中，这会给我更多的行。否则我会得到

column X must appear in the GROUP BY clause or be used in an aggregate function

错误。

有什么想法吗？

Answer 1

这是迁移到 Pg 的常见问题，因为 MySQL 允许您编写可能具有不确定结果的非标准 GROUP BY 子句。

如果你:

SELECT a, b FROM sometable GROUP BY a;

并且表中的 b 有 >1 个不同的值，数据库如何知道要返回哪一行？

对于 PostgreSQL，如果你想要这种行为 you must use the DISTINCT ON extension instead ，因为 PostgreSQL 通过拒绝可能不确定的 GROUP BY 子句来遵循标准。像这样的东西:

SELECT DISTINCT ON (a) a, b FROM sometable ORDER BY a;

...假设这确实是您想要的。你的问题并没有真正涵盖你试图解决的根本问题 - 为什么你试图这样做 - 所以很难说这是否是正确的方法。

---

更新:根据评论中的解释，在您的情况下，我认为您可以将相关子查询与 ORDER BY ... LIMIT 1 和相等性测试结合使用，而不是您当前与 IN 不相关的子查询。

将你的 Rails 代码翻译成 SQL，我得到类似的东西:

SELECT m.derp, c.id
FROM c
INNER JOIN m ON m.c_id = c.id
WHERE m.id IN (SELECT max(id) from m group by c_id order by updated_at desc)
ORDER BY m.updated_at desc;

您解释说这是为了在每次对话中查找最新消息。

如果是这样，请尝试:

SELECT m.derp, c.id
FROM c
INNER JOIN m ON m.c_id = c.id
WHERE m.id = (SELECT m2.id FROM m m2 WHERE m2.c_id = c.id ORDER BY m2.updated_at LIMIT 1)
ORDER BY m.updated_at desc;

子查询对每个外部 c 行运行一次，因此 m.c_id 有一个索引是至关重要的。事实上，我认为它会在索引上表现最佳

m (c_id, updated_at)

或者如果您使用的是支持仅索引扫描的 PostgreSQL 9.2 或更高版本，则可能:

m (c_id, updated_at, id)

尽管我需要用虚拟数据和模式测试它们才能 100% 确定。