如何在SQL中使用CASE生成的PerformanceTotal计算新列？

你可以通过以下几种方式实现AmountDifference列的计算，核心解决SQL中无法直接引用同SELECT子句内刚计算列的问题：

方法1：重复CASE表达式计算 #

直接在SELECT中重复PerformanceTotal的计算逻辑，再减去total值，这种方式适配所有SQL方言：

select
    EmployeeNumber,
    Approvals as TotalApprovals,
    base,
    bonus,
    base + bonus as total,
    case
        when Approvals <= 19 then approvals * 50 + 0
        when approvals > 19 and approvals <= 39 then ((approvals - 19) * 70) + (50*19)
        when approvals > 39 and approvals <= 59 then ((approvals - 39) * 80) + (50*19) + (70*20)
        when approvals > 59 and approvals <= 99 then ((approvals - 59) * 90) + (50*19) + (70*20) + (80*20)
        when approvals > 99 then ((approvals - 99) * 100) + (50*19) + (70*20) + (80*20) + (90*40)
    end as PerformanceTotal,
    -- 计算差值：复用PerformanceTotal的CASE逻辑
    (
        case
            when Approvals <= 19 then approvals * 50 + 0
            when approvals > 19 and approvals <= 39 then ((approvals - 19) * 70) + (50*19)
            when approvals > 39 and approvals <= 59 then ((approvals - 39) * 80) + (50*19) + (70*20)
            when approvals > 59 and approvals <= 99 then ((approvals - 59) * 90) + (50*19) + (70*20) + (80*20)
            when approvals > 99 then ((approvals - 99) * 100) + (50*19) + (70*20) + (80*20) + (90*40)
        end
    ) - (base + bonus) as AmountDifference
from
(select
EmployeeNumber,
sum(applicationsapproved) as Approvals,
sum(BaseCalculation) as Base,
sum(BonusCalcuation) as Bonus
from compensation
where EmployeeNumber = '100032' and ActivityYear='2019'
and ApplicationsApproved > 0
group by EmployeeNumber
) as aa

方法2：用子查询/CTE复用计算结果 #

将原查询的计算结果封装为子查询或CTE，这样就能直接引用已计算好的PerformanceTotal和total列，代码更简洁易维护：

子查询实现 #

select
    EmployeeNumber,
    TotalApprovals,
    base,
    bonus,
    total,
    PerformanceTotal,
    PerformanceTotal - total as AmountDifference
from (
    select
        EmployeeNumber,
        Approvals as TotalApprovals,
        base,
        bonus,
        base + bonus as total,
        case
            when Approvals <= 19 then approvals * 50 + 0
            when approvals > 19 and approvals <= 39 then ((approvals - 19) * 70) + (50*19)
            when approvals > 39 and approvals <= 59 then ((approvals - 39) * 80) + (50*19) + (70*20)
            when approvals > 59 and approvals <= 99 then ((approvals - 59) * 90) + (50*19) + (70*20) + (80*20)
            when approvals > 99 then ((approvals - 99) * 100) + (50*19) + (70*20) + (80*20) + (90*40)
        end as PerformanceTotal
    from
    (select
    EmployeeNumber,
    sum(applicationsapproved) as Approvals,
    sum(BaseCalculation) as Base,
    sum(BonusCalcuation) as Bonus
    from compensation
    where EmployeeNumber = '100032' and ActivityYear='2019'
    and ApplicationsApproved > 0
    group by EmployeeNumber
    ) as aa
) as bb

CTE（公共表表达式）实现（适配MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的数据库） #

with calc_data as (
    select
        EmployeeNumber,
        Approvals as TotalApprovals,
        base,
        bonus,
        base + bonus as total,
        case
            when Approvals <= 19 then approvals * 50 + 0
            when approvals > 19 and approvals <= 39 then ((approvals - 19) * 70) + (50*19)
            when approvals > 39 and approvals <= 59 then ((approvals - 39) * 80) + (50*19) + (70*20)
            when approvals > 59 and approvals <= 99 then ((approvals - 59) * 90) + (50*19) + (70*20) + (80*20)
            when approvals > 99 then ((approvals - 99) * 100) + (50*19) + (70*20) + (80*20) + (90*40)
        end as PerformanceTotal
    from
    (select
    EmployeeNumber,
    sum(applicationsapproved) as Approvals,
    sum(BaseCalculation) as Base,
    sum(BonusCalcuation) as Bonus
    from compensation
    where EmployeeNumber = '100032' and ActivityYear='2019'
    and ApplicationsApproved > 0
    group by EmployeeNumber
    ) as aa
)
select
    *,
    PerformanceTotal - total as AmountDifference
from calc_data

方法3：使用CROSS APPLY（仅适配SQL Server、PostgreSQL 12+等支持的数据库） #

通过CROSS APPLY一次性计算PerformanceTotal，然后在主查询中直接复用该结果：

select
    aa.EmployeeNumber,
    aa.Approvals as TotalApprovals,
    aa.base,
    aa.bonus,
    aa.base + aa.bonus as total,
    pt.PerformanceTotal,
    pt.PerformanceTotal - (aa.base + aa.bonus) as AmountDifference
from
(select
EmployeeNumber,
sum(applicationsapproved) as Approvals,
sum(BaseCalculation) as Base,
sum(BonusCalcuation) as Bonus
from compensation
where EmployeeNumber = '100032' and ActivityYear='2019'
and ApplicationsApproved > 0
group by EmployeeNumber
) as aa
cross apply (
    select
        case
            when aa.Approvals <= 19 then aa.approvals * 50 + 0
            when aa.approvals > 19 and aa.approvals <= 39 then ((aa.approvals - 19) * 70) + (50*19)
            when aa.approvals > 39 and aa.approvals <= 59 then ((aa.approvals - 39) * 80) + (50*19) + (70*20)
            when aa.approvals > 59 and aa.approvals <= 99 then ((aa.approvals - 59) * 90) + (50*19) + (70*20) + (80*20)
            when aa.approvals > 99 then ((aa.approvals - 99) * 100) + (50*19) + (70*20) + (80*20) + (90*40)
        end as PerformanceTotal
) as pt

内容的提问来源于stack exchange，提问作者Masha J. Karabinovich