|
|
相关文章推荐
|
力能扛鼎的火龙果 · Xcode 9 ...· 6 月前 · |
|
|
独立的电影票 · [油管搬运] C# WPF UI | ...· 8 月前 · |
|
|
失望的扁豆 · 社区发现法的python代码 - CSDN文库· 11 月前 · |
|
|
霸气的骆驼 · 如何在SwiftUI中获得呈现的实际视图大小 ...· 1 年前 · |
|
|
傻傻的楼梯 · Spring Data Neo4j ...· 1 年前 · |
我想运行这个查询：
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表达式必须与初始ORDER BY表达式匹配
添加
address_id
作为第一个
ORDER BY
表达式可以消除这个错误，但我真的不想在
address_id
上添加排序。是否可以不按
address_id
排序
7 个回答
高票数
最新
Stack Overflow用户
修改于2014-08-14 19:44
得票数
68
可以在子查询中按address_id排序，然后在外部查询中按所需排序。
SELECT * FROM
(SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM "purchases"
WHERE "purchases"."product_id" = 1 ORDER BY address_id DESC )
ORDER BY purchased_at DESC
Stack Overflow用户
修改于2017-07-13 12:25
得票数
265
文档中写道：
表达式DISTINCT ON (
，... )只保留给定表达式求值为相等的每组行中的第一行。..。请注意，除非使用ORDER BY来确保所需的行先出现，否则每个集合的“第一行”是不可预测的。..。DISTINCT ON表达式必须与最左侧的ORDER BY表达式匹配。
因此，您必须将
address_id
添加到order by。
或者，如果您正在查找包含每个
address_id
的最新购买产品的整行，并且该结果按
purchased_at
排序，那么您将尝试解决每组最大N的问题，该问题可以通过以下方法解决：
应该在大多数DBMS中工作的一般解决方案：
SELECT t1.* FROM purchases t1
JOIN (
SELECT address_id, max(purchased_at) max_purchased_at
FROM purchases
WHERE product_id = 1
GROUP BY address_id
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC一个更加面向PostgreSQL的解决方案，基于@hkf的回答：
SELECT * FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY address_id, purchased_at DESC
ORDER BY purchased_at DESC问题在这里得到澄清、扩展和解决： Selecting rows ordered by some column and distinct on another
Stack Overflow用户
修改于2020-08-25 22:53
得票数
66
子查询 可以解决这个问题：
SELECT *
FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY purchased_at DESC;
ORDER BY
中的前导表达式必须与
DISTINCT ON
中的列一致，因此不能按同一
SELECT
中的不同列排序。
如果要从每个集合中选取特定行，则仅在子查询中使用额外的
ORDER BY
：
SELECT *
FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY address_id, purchased_at DESC -- get "latest" row per address_id
ORDER BY purchased_at DESC;
如果
purchased_at
可以为
NULL
，则使用
DESC NULLS LAST
-并匹配您的索引以获得最佳性能。请参见：
- Sort by column ASC, but NULL values first?
- Why does ORDER BY NULLS LAST affect the query plan on a primary key?
相关的，有更多解释：
Stack Overflow用户
回答于2013-07-18 14:38
得票数
11
窗口函数可以在一次遍历中解决这个问题：
SELECT DISTINCT ON (address_id)
LAST_VALUE(purchases.address_id) OVER wnd AS address_id
FROM "purchases"
WHERE "purchases"."product_id" = 1
WINDOW wnd AS (
PARTITION BY address_id ORDER BY purchases.purchased_at DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Stack Overflow用户
修改于2021-04-20 03:29
得票数
8
对于任何使用 Flask-SQLAlchemy 的人来说，这对我很有效
from app import db
from app.models import Purchases
from sqlalchemy.orm import aliased
from sqlalchemy import desc
stmt = Purchases.query.distinct(Purchases.address_id).subquery('purchases')
alias = aliased(Purchases, stmt)
distinct = db.session.query(alias)
distinct.order_by(desc(alias.purchased_at))
Stack Overflow用户
回答于2017-05-23 12:35
得票数
-2
您也可以使用group by子句来完成此操作
SELECT purchases.address_id, purchases.* FROM "purchases"
WHERE "purchases"."product_id" = 1 GROUP BY address_id,
purchases.purchased_at ORDER purchases.purchased_at DESC