模型Subscription has_many SubscriptionCart。
ASubscriptionCart有一個status和一個authorized_at日期。
我需要authorized_at從與 a 關聯的所有購物車中選擇日期最早的購物車Subscription，然后我必須Subscription按此subscription_carts.authorized_at列對所有回傳的結果進行排序。
下面的查詢正在運行，但我不知道如何選擇DISTINCT ON subscription.id以避免重復，但是ORDER BY subscription_carts.authorized_at.
到目前為止的原始 sql 查詢：
select distinct on (s.id) s.id as subscription_id, subscription_carts.authorized_at, s.*
from subscriptions s
join subscription_carts subscription_carts on subscription_carts.subscription_id = s.id
and subscription_carts.plan_id = s.plan_id
where subscription_carts.status = 'processed'
and s.status IN ('authorized','in_trial', 'paused')
order by s.id, subscription_carts.authorized_at
如果我先嘗試ORDER BY subscription_carts.authorized_at，我會收到一個錯誤，因為DISTINCT ON和ORDER BY運算式必須按相同的順序。
我發現的解決方案對于我所需要的來說似乎太復雜了，我未能實施它們，因為我不完全理解它們。
GROUP BY subscription_id然后從該組中挑選而不是使用會更好DISTINCT ON嗎？任何幫助表示贊賞。
uj5u.com熱心網友回復：
這個要求是DISTINCT ON作業所必需的；要更改最終順序，您可以添加帶有另一個ORDER BY子句的外部查詢：
SELECT *
FROM (SELECT DISTINCT ON (s.id)
s.id as subscription_id, subscription_carts.authorized_at, s.*
FROM subscriptions s
JOIN ...
WHERE ...
ORDER BY s.id, subscription_carts.authorized_at
) AS subq
ORDER BY authorized_at;
uj5u.com熱心網友回復：
您不必使用DISTINCT ON. 雖然它偶爾有用，但我個人認為基于視窗函式的方法更清晰：
-- Optionally, list all columns explicitly, to remove the rn column again
SELECT *
FROM (
SELECT
s.id AS subscription_id,
c.authorized_at,
s.*,
ROW_NUMBER () OVER (PARTITION BY s.id ORDER BY c.authorized_at) rn
FROM subscriptions s
JOIN subscription_carts c
ON c.subscription_id = s.id
AND c.plan_id = s.plan_id
WHERE c.status = 'processed'
AND s.status IN ('authorized', 'in_trial', 'paused')
) t
WHERE rn = 1
ORDER BY subscription_id, authorized_at
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/364921.html
標籤：sql PostgreSQL的 独特的
上一篇：IonicCordovaAndroid：找不到可用的Android構建工具。30.x安裝的最高版本是30.0.2；所需的最低版本為30.0.3