PostgreSQL计划报表条件查询需求：多币种金额转换展示

需求说明 #

生成计划报表时需满足：

• 保留plan表的所有原始条目
• 若计划的plan_currency在foreign_exchange_rates表中作为from_currency存在，额外添加转换为对应to_currency的金额条目（金额=原金额×对应汇率）

解决方案 #

先获取每种货币对的最新有效汇率，再通过UNION ALL合并原计划条目与转换后的条目：

WITH latest_exchange_rates AS (
    -- 获取每个(from_currency, to_currency)的最新有效汇率
    SELECT DISTINCT ON (from_currency, to_currency)
           from_currency,
           to_currency,
           conversion_rate
    FROM foreign_exchange_rates
    WHERE effective_date <= CURRENT_DATE  -- 筛选当前及之前生效的汇率
    ORDER BY from_currency, to_currency, effective_date DESC  -- 按货币对分组，取最新生效的汇率
)
-- 合并原条目与转换后条目
SELECT
    plan_id,
    name,
    display_name,
    plan_currency AS currency,
    amount AS calculated_amount,
    'original' AS entry_type  -- 标记条目类型，可选
FROM plan
UNION ALL
SELECT
    p.plan_id,
    p.name,
    p.display_name,
    ler.to_currency AS currency,
    p.amount * ler.conversion_rate AS calculated_amount,
    'converted' AS entry_type  -- 标记转换后的条目
FROM plan p
JOIN latest_exchange_rates ler 
    ON p.plan_currency = ler.from_currency
ORDER BY plan_id, entry_type;

关键逻辑说明 #

1. 最新汇率获取：使用DISTINCT ON (from_currency, to_currency)结合ORDER BY effective_date DESC，确保每个货币转换对只保留最近生效的汇率记录
2. 条目合并：
   • 第一部分查询直接返回plan表原始数据，保留所有字段
   • 第二部分通过JOIN汇率表计算转换金额，生成新条目
   • 用UNION ALL而非UNION，避免因数据巧合重复导致条目丢失
3. 可选优化：
   • 若需过滤特定租户/实体的汇率，可在latest_exchange_rates的WHERE子句中添加tenant_id = '目标租户ID' AND entity_id = '目标实体ID'
   • 若需处理amount为NULL的情况，可使用COALESCE(p.amount, 0) * ler.conversion_rate避免计算结果为NULL

内容的提问来源于stack exchange，提问作者Rax