如何根據狀態值有條件地選擇一行？

我想選擇所有可用和購買的包。使用者可以打開兩個頁面購買一包，這會導致付款表中有兩行。一個狀態為“待定”，購買的狀態為“已付款”。我創建了下面的程式碼來顯示 1 個使用者的所有可用和付費包。

select distinct p.id_pack, pa.status,
   CASE
       WHEN pa.status = 'paid' THEN 'Paid'
       WHEN pa.status = 'pending' THEN 'Available'
       WHEN pa.status ISNUll THEN 'Available'
       END Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
order by p.id_pack

目前結果

不應選擇第三行 (id_pack 2, status2 ‘Available’)，因為該包已被使用者購買（創建該行是因為打開了兩個購買頁面）。

預期結果

任何人都知道如何做到這一點？

更新

我從 jjanes 執行程式碼。結果在圖像下方。

select p.id_pack,  u.id_user,
   max(CASE
       WHEN pa.status = 'paid' THEN 'Paid'
       WHEN pa.status <> 'paid' THEN 'Available'
       WHEN pa.status isnull then 'Available'
       END) as Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
group by p.id_pack, u.id_user
order by p.id_pack

不知何故，它為每一行創建了兩個條目，並將“可用”轉換為“付費”（？）。我有點不知道為什麼會這樣。

找到了我的問題的答案。

SELECT allPacks.id_pack, allPacks.name, allPacks.price, allPacks.user, allPacks.discount, allPacks.preview_url, allPacks.type, allPacks.description,
   CASE
       WHEN allPaid.status = 'paid' THEN 'Paid'
       WHEN allPaid.status = 'pending' THEN 'Available'
       WHEN allPaid.status ISNUll THEN 'Available'
   END payment_status
FROM (select * from packs) as allPacks
left join (select * from packs pack, payment pay, users u 
   where pay.status='paid' 
   and pay.pack=pack.id_pack 
   and u.id_user=pay.user
   and u.cognito_id='x'
   and pay.user=1) as allPaid
   on allPacks.id_pack = allPaid.pack

對於回复的人，謝謝:)

引用自：https://dba.stackexchange.com/questions/302554