PostgreSQL多表关联后按条件聚合实现单SQL查询金额统计结果

SELECT 
  hw.worker_id,
  SUM(
    CASE WHEN cg.payment_status = '1' 
    THEN (rr.base_price * ce.euro_value) * (1 + cg.percentage_price_adjustment / 100.0)
    ELSE 0 
    END
  ) AS total_paid,
  SUM(
    CASE WHEN cg.payment_status = '0' 
    THEN (rr.base_price * ce.euro_value) * (1 + cg.percentage_price_adjustment / 100.0)
    ELSE 0 
    END
  ) AS total_outstanding,
  SUM(
    (rr.base_price * ce.euro_value) * (1 + cg.percentage_price_adjustment / 100.0)
  ) AS total_pledged
FROM currency_exchange ce
INNER JOIN HealthWorkers hw 
  ON ce.currency = hw.preferred_currency
INNER JOIN community_grants cg 
  ON hw.worker_id = cg.worker_id
INNER JOIN region_reference rr 
  ON rr.community_id = cg.community_id 
  AND rr.region_id = cg.region_id
WHERE 
  hw.worker_id = 'malawi_01' 
  AND cg.chosen = true
GROUP BY hw.worker_id;

逻辑说明 #

• 联表逻辑完全复用你提供的可运行查询，仅调整了联表写法提升可读性，执行结果和原有查询完全一致
• 用CASE WHEN实现条件聚合，直接在数据库层完成统计，不需要返回中间明细再在代码层遍历计算：
  • 计算total_paid时仅累加支付状态为1的记录金额
  • 计算total_outstanding时仅累加支付状态为0的记录金额
  • total_pledged直接累加所有符合筛选条件的记录金额，等价于前两个字段的求和结果
• 金额计算默认包含了percentage_price_adjustment百分比调整规则，如果你不需要该调整，直接把金额计算部分替换为rr.base_price * ce.euro_value即可
• 因为你查询的是单个工作人员的统计结果，也可以去掉GROUP BY子句，不影响返回结果

样例数据计算结果参考 #

基于你提供的样例数据，返回结果如下：

注：region_id=3的社区补助记录因不在region_reference关联范围内，会被INNER JOIN过滤，未计入统计。

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