PostgreSQL中的CASE WHEN表达式

Question

我有两个表"prods"；(id，price(和"；产品_价格"；(id，prod_id，price(。有必要显示"；prods"；。prods_prices中每个产品的最低价格，如果没有，则为"prods"价格"。我做了，但我认为这是错误的。

SELECT DISTINCT "prods"."id" as "id", "prods"."price",
CASE
WHEN (SELECT min("prods_prices"."price") FROM "prods_prices"  WHERE "prods_prices"."prod_id"=id) isnull THEN "prods"."price"
ELSE (SELECT min("prods_prices"."price") FROM "prods_prices"  WHERE "prods_prices"."prod_id"=id)
END 
AS "price",
FROM "prods" 

Answer 1

如果id是prods上的主键，则：

select   prods.id, 
coalesce(min(prods_prices.price), prods.price) price
from     prods 
left join prods_prices on prods_prices.prod_id = prods.id
group by prods.id

如果min(prods_prices.price)为空，则Coalesce返回prods.price。

Answer 2

根据您提供的少量信息，您可以进行下一次查询(此处为结果(

with min_price as (select prod_id,min(price) as price from prod_prices group by prod_id)
select p.id,case when mp is null then p.price else mp.price end as price
from prods p left join min_price mp on p.id = mp.prod_id