Oracle的PIVOT透视表查询如何转换为等效PostgreSQL语句

问题核心原因 #

你当前的写法将CASE判断放在SUM聚合函数外层，且按app_status分组，导致每个状态的计算结果拆分到独立行，和Oracle PIVOT返回单行聚合结果的逻辑不符。

调整后完整等效语句 #

CREATE OR REPLACE VIEW PROGRAM_EXPENDITURES AS
SELECT
    -- 等效Oracle的ROWNUM，因为透视结果只有1行，直接生成唯一ID
    ROW_NUMBER() OVER() AS ID,
    8400000 AS PROGRAM_TOTAL,
    SUM(CASE WHEN app_status = 'approved' THEN incentive_amt_num ELSE 0 END) AS approved_incentive,
    SUM(CASE WHEN app_status = 'pending' THEN incentive_amt_num ELSE 0 END) AS pending_incentive,
    SUM(CASE WHEN app_status = 'void' THEN incentive_amt_num ELSE 0 END) AS void_incentive,
    SUM(CASE WHEN app_status = 'approved' THEN program_income ELSE 0 END) AS approved_income,
    SUM(CASE WHEN app_status = 'pending' THEN program_income ELSE 0 END) AS pending_income,
    SUM(CASE WHEN app_status = 'void' THEN program_income ELSE 0 END) AS void_income
FROM WOODBURN.HB357;

逻辑说明 #

• 将SUM聚合函数套在CASE判断外层，对全表中符合对应app_status的字段值求和，不符合的行取值为0，不会影响求和结果
• 移除GROUP BY app_status配置，所有聚合计算都是对全表扫描后的统计，最终仅返回1行结果，和原Oracle PIVOT的输出结构、数据完全一致
• 注意app_status的取值大小写需要和表中实际存储的内容匹配，如果你表中存储的是首字母大写的Approved/Pending/Void，对应调整CASE判断里的字符串即可

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