因为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")
基本上我需要把m按c分组,按最近创建的m排序。Postgres不会让我选择多个列,而不是通过调用它们,这将给我更多的行比我想要的。否则我将得到
column X must appear in the GROUP BY clause or be used in an aggregate function
错误。
任何想法?
这是一个迁移到Pg的常见问题,因为MySQL允许您编写可能具有不确定性结果的非标准GROUP BY
子句。
SELECT a, b FROM sometable GROUP BY a;
并且表中b
有>1个不同的值,数据库如何知道返回哪一行?
对于PostgreSQL,如果你想要这种行为,你必须使用DISTINCT ON
扩展代替,因为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%的肯定。