Postgresql DISTINCT ON使用不同的ORDER BY

发布时间：2020-12-13 16:54:17 所属栏目：百科来源：网络整理

导读：我想运行查询： SELECT DISTINCT ON (address_id) purchases.address_id,purchases.*FROM "purchases"WHERE "purchases"."product_id" = 1ORDER BY purchases.purchased_at DESC 我收到错误： PG::Error: ERROR: SELECT DISTINCT ON expressions must match

我想运行查询：

SELECT DISTINCT ON (address_id) purchases.address_id,purchases.*
FROM "purchases"
WHERE "purchases"."product_id" = 1
ORDER BY purchases.purchased_at DESC

我收到错误：

PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

添加排序在address_id修复问题，但我真的不想添加排序通过address_id。是否可以不通过address_id进行排序？

文档说：

DISTINCT ON ( expression [,…] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […] Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

Official documentation

所以你必须添加address_id到order by。

这个查询可能会：

SELECT DISTINCT ON (address_id) purchases.address_id,purchases.*
FROM "purchases"
WHERE "purchases"."product_id" = 1
ORDER BY address_id,purchases.purchased_at DESC

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!