postgresql - 有条件地从oneToMany中选择

Question

如果没有以原价选择它，我会尝试在有报价时从商品表中选择商品。为此，我有 2 个表 items 和 priceList，这是一个例子:

| itemId | itemName      | 
|--------|---------------|
| 1      | bikex1        |
| 2      | bikex2        |
| 3      | bikex3        |
| 4      | bikex4        | 


| priceIDId | itemID   | itemPrice | priceStatus |
|-----------|----------|-----------|-------------|
| 1         | 1        |100        | offer       |
| 2         | 1        |150        | orignalPrice|
| 4         | 2        |200        | orignalPrice|

因为 bikex2 没有报价，我将获得原始价格 (200)，但对于 bikex1，我将获得报价 (100)

| priceIDId | itemID   | itemPrice | priceStatus |
|-----------|----------|-----------|-------------|
| 1         | 1        |100        | offer       |
| 4         | 2        |200        | orignalPrice|

我试过的代码如下:

select * from priceList where priceStatus = 'offer' union all select * from priceList where priceStatus = 'orignalPrice' AND  NOT EXISTS (select * from priceList where priceStatus = 'offer')

Answer 1

你可以左连接两次:

select 
    i.*,
    coalesce(p1.itemPrice, p2.itemPrice) itemPrice,
    coalesce(p1.priceStatus, p2.priceStatus) priceStatus
from items i
left join priceList p1 on p1.itemID = i.itemID and p1.priceStatus = 'offer'
left join priceList p2 on p2.itemID = i.itemID and p1.priceStatus = 'originalPrice'
where coalesce(p1.itemID , p2.itemID) is not null -- at least one of the joins should match

另一种选择是使用带条件排序的窗口函数:

select itemID, itemName, itemPrice, priceStatus
from (
    select 
        i.*,
        p.itemPrice,
        p.priceStatus,
        row_number() over(
            partition by p.itemID 
            order by case when p.priceStatus = 'offer' then 0 else 1 
        end) rn
    from items i
    inner join priceList p on p.itemID = i.itemID
) t
where rn = 1