如何简化municipaltax、education及education requisition allowance的求和计算？

方法1：通过CTE+条件聚合一次性获取所有税率，避免重复子查询 #

先从EdmontonPropertyTaxRate中筛选出2022年的目标税率，将其转为独立列，再与主表epa关联，直接基于这些税率列计算各项税费及总和：

WITH tax_rates AS (
    SELECT
        MAX(CASE WHEN tax_rate_type = 'municipal' THEN tax_rate END) AS municipal_rate,
        MAX(CASE WHEN tax_rate_type = 'education' THEN tax_rate END) AS education_rate,
        MAX(CASE WHEN tax_rate_type = 'education requisition allowance' THEN tax_rate END) AS education_requisition_allowance_rate
    FROM EdmontonPropertyTaxRate
    WHERE tax_year = 2022
)
SELECT
    epa.assessed_value * tr.municipal_rate AS 'municipaltax',
    epa.assessed_value * tr.education_rate AS 'education',
    epa.assessed_value * tr.education_requisition_allowance_rate AS 'education requisition allowance',
    epa.assessed_value * (tr.municipal_rate + tr.education_rate + tr.education_requisition_allowance_rate) AS 'total tax'
FROM epa
CROSS JOIN tax_rates tr;

方法2：先聚合总税率，再推导分项与总和 #

如果需要简化总税费的计算逻辑，可以先算出三个税率的总和，再结合分项税率完成计算：

WITH target_rates AS (
    SELECT tax_rate_type, tax_rate
    FROM EdmontonPropertyTaxRate
    WHERE tax_year = 2022
      AND tax_rate_type IN ('municipal', 'education', 'education requisition allowance')
),
total_rate AS (
    SELECT SUM(tax_rate) AS total_tax_rate FROM target_rates
)
SELECT
    epa.assessed_value * (SELECT tax_rate FROM target_rates WHERE tax_rate_type = 'municipal') AS 'municipaltax',
    epa.assessed_value * (SELECT tax_rate FROM target_rates WHERE tax_rate_type = 'education') AS 'education',
    epa.assessed_value * (SELECT tax_rate FROM target_rates WHERE tax_rate_type = 'education requisition allowance') AS 'education requisition allowance',
    epa.assessed_value * tr.total_tax_rate AS 'total tax'
FROM epa
CROSS JOIN total_rate tr;

优化说明 #

• 彻底消除原代码中重复执行相同子查询的冗余问题，大幅提升查询效率
• 用CTE封装税率逻辑，让代码结构更清晰，后续修改税率类型或年份时只需调整一处
• 总税费通过「税率求和后再乘评估值」实现，比原代码的分项相加更简洁高效

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