pg的with查询结合COALESCE将未查询到记录的内容默认置零

pg的with查询结合COALESCE将未查询到记录的内容默认置零

with t as (
    select ('CNY') union select ('USD')union select ('EUR') union select ('JPY') union select ('HKD') 
)
select t."?column?" as currency,case when amount is null then 0 else amount end as amount
from t left join today_pay_amount p on t."?column?" = p.currency 

with t as (
    select ('CNY') union select ('USD')union select ('EUR') union select ('JPY') union select ('HKD') 
)
select t."?column?" as currency,COALESCE(p.amount, 0) as amount
from t left join today_pay_amount p on t."?column?" = p.currency 

简单结果可以用 SELECT 
    decode(count(amount), 1, SUM(amount), 0)
FROM B 解决