按行动态计算多列加班工资总和（含部分审批逻辑）

现有一张加班时长计算表，字段包括id、worker_id、overtime_15x（1.5倍加班时长）、salary_15x（1.5倍对应总工资）、overtime_2x（2倍加班时长）、salary_2x（2倍对应总工资）、overtime_3x（3倍加班时长）、salary_3x（3倍对应总工资）、is_approved（审批状态：yes/no/part）、approved_amount（部分审批时的批准时长）。

需求规则：

• 若is_approved = 'yes'：总工资为所有倍率工资总和（salary_15x + salary_2x + salary_3x）
• 若is_approved = 'no'：总工资为0
• 若is_approved = 'part'：从高倍率到低倍率（3x→2x→1.5x）依次抵扣批准时长，计算对应工资。例如批准6小时，先全额计入3x的3小时工资180，再计入2x的2小时工资100，最后计入1.5x的1小时工资25，总计305。

常规CASE语句无法处理这种阶梯式的剩余时长抵扣逻辑，以下是可行的SQL实现方案：

分步计算逻辑 #

对于part状态，通过分步计算每一档倍率的抵扣时长和对应工资，逐步消耗批准时长：

1. 优先处理3x倍率：计算可抵扣时长为LEAST(overtime_3x, approved_amount)，对应工资为该时长占3x总时长的比例乘以3x总工资（需避免除以0）
2. 计算抵扣3x后的剩余时长：approved_amount - LEAST(overtime_3x, approved_amount)
3. 用剩余时长处理2x倍率：取剩余时长和2x总时长的较小值，计算对应工资
4. 再计算抵扣2x后的剩余时长，处理1.5x倍率
5. 将三部分工资相加得到part状态的总工资

SQL代码实现 #

SELECT
    id,
    worker_id,
    CASE is_approved
        WHEN 'yes' THEN salary_15x + salary_2x + salary_3x
        WHEN 'no' THEN 0
        WHEN 'part' THEN
            -- 3x抵扣工资
            COALESCE((LEAST(overtime_3x, approved_amount) / NULLIF(overtime_3x, 0)) * salary_3x, 0)
            +
            -- 2x抵扣工资（先算剩余时长）
            COALESCE((LEAST(overtime_2x, approved_amount - LEAST(overtime_3x, approved_amount)) / NULLIF(overtime_2x, 0)) * salary_2x, 0)
            +
            -- 1.5x抵扣工资（最后剩余时长）
            COALESCE((LEAST(overtime_15x, 
                approved_amount - LEAST(overtime_3x, approved_amount) - LEAST(overtime_2x, approved_amount - LEAST(overtime_3x, approved_amount))
            ) / NULLIF(overtime_15x, 0)) * salary_15x, 0)
        END AS approved_total_salary
FROM overtime_table;

优化写法（简化重复计算） #

用子查询预计算每一步的剩余时长，让代码更易读：

WITH overtime_steps AS (
    SELECT
        id,
        worker_id,
        is_approved,
        approved_amount,
        overtime_15x,
        salary_15x,
        overtime_2x,
        salary_2x,
        overtime_3x,
        salary_3x,
        -- 3x已用时长
        LEAST(overtime_3x, approved_amount) AS used_3h,
        -- 抵扣3x后剩余时长
        GREATEST(approved_amount - LEAST(overtime_3x, approved_amount), 0) AS remain_after_3h
    FROM overtime_table
)
SELECT
    id,
    worker_id,
    CASE is_approved
        WHEN 'yes' THEN salary_15x + salary_2x + salary_3x
        WHEN 'no' THEN 0
        WHEN 'part' THEN
            COALESCE((used_3h / NULLIF(overtime_3x, 0)) * salary_3x, 0)
            +
            COALESCE((LEAST(overtime_2x, remain_after_3h) / NULLIF(overtime_2x, 0)) * salary_2x, 0)
            +
            COALESCE((LEAST(overtime_15x, GREATEST(remain_after_3h - LEAST(overtime_2x, remain_after_3h), 0)) / NULLIF(overtime_15x, 0)) * salary_15x, 0)
        END AS approved_total_salary
FROM overtime_steps;

关键注意点 #

• 使用NULLIF(overtime_xx, 0)配合COALESCE避免除以0的报错
• 用GREATEST(..., 0)确保剩余时长不会出现负数

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