SQL按material分组实现qty1扣除qty2-qty3差值的逻辑求助

需求说明 #

针对每个material（物料）执行以下逻辑：

• 仅当qty3不为空时，计算需扣除的总量：扣除总量 = qty2 - qty3
• 优先从当前行的qty1中扣除对应量：若当前行qty1充足，扣除后得到finalQty
• 若当前行qty1不足以覆盖扣除总量，则按marks排序的最后一行qty1至少保留10，剩余扣除量从同物料的其他行（按marks升序顺序）依次扣除，直到扣完总量。

示例测试数据 #

DECLARE @items TABLE(qty1 INT, qty2 INT, qty3 INT, marks INT, material varchar(300));
INSERT INTO @items (qty1, qty2, qty3, marks, material)
VALUES
(120, 320, null, 10,  'a')
,(60, 320, null, 20,  'a')
,(80, 320, null,  30, 'a')
,(60, 320, 50, 40,  'a')

,(120, 320, null,  10,  'b')
,(60, 320, null,  20,  'b')
,(80, 320, null,  30,  'b')
,(60, 320, 300, 40, 'b')

初始尝试代码（存在逻辑缺陷） #

select case when  qty2 > qty3 and qty3 is not null then 
            case when qty1 > (qty2-qty3) then
                qty1 - (qty2-qty3)
            else
                lead(qty1-qty3) OVER (PARTITION BY materialId ORDER BY marks) 
            end
        else
            qty1
        end as finalQty, material
     from @items

注：该代码存在多个问题：引用了不存在的materialId字段、lead函数无法实现跨行扣除逻辑、未处理最后一行保留10的规则。

预期结果 #

finalQty  material
20        a
10        a
10        a
10        a
120       b
60        b
80        b
40        b

正确SQL解决方案 #

WITH material_deduction AS (
    -- 计算每个物料的总扣除量，仅取有qty3的行的扣除值
    SELECT 
        material,
        MAX(CASE WHEN qty3 IS NOT NULL THEN qty2 - qty3 ELSE 0 END) AS total_deduct
    FROM @items
    GROUP BY material
),
ordered_items AS (
    -- 对每个物料按marks排序，标记是否为最后一行，计算累计可用扣除量
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY material ORDER BY marks) AS rn,
        COUNT(*) OVER (PARTITION BY material) AS total_rows,
        SUM(CASE 
                WHEN ROW_NUMBER() OVER (PARTITION BY material ORDER BY marks) = COUNT(*) OVER (PARTITION BY material) 
                THEN qty1 - 10 
                ELSE qty1 
            END) OVER (PARTITION BY material ORDER BY marks) AS cumulative_available
    FROM @items
)
SELECT 
    oi.qty1 - 
    CASE 
        -- 无扣除需求时不扣
        WHEN md.total_deduct <= 0 THEN 0
        -- 最后一行最多扣到10
        WHEN oi.rn = oi.total_rows THEN 
            CASE 
                WHEN (md.total_deduct - (oi.cumulative_available - (oi.qty1 - 10))) <= (oi.qty1 - 10) 
                THEN md.total_deduct - (oi.cumulative_available - (oi.qty1 - 10)) 
                ELSE oi.qty1 - 10 
            END
        -- 非最后一行，按剩余扣除量计算应扣部分
        ELSE 
            CASE 
                WHEN oi.qty1 >= (md.total_deduct - LAG(oi.cumulative_available, 1, 0) OVER (PARTITION BY oi.material ORDER BY oi.rn)) 
                THEN md.total_deduct - LAG(oi.cumulative_available, 1, 0) OVER (PARTITION BY oi.material ORDER BY oi.rn) 
                ELSE oi.qty1 
            END
    END AS finalQty,
    oi.material
FROM ordered_items oi
JOIN material_deduction md ON oi.material = md.material
ORDER BY oi.material, oi.rn;

逻辑说明 #

1. material_deduction CTE：提取每个物料的总扣除量（仅取存在qty3的行的qty2-qty3值）。
2. ordered_items CTE：对每个物料的行按marks排序，标记行号与总行数，同时计算累计可用于扣除的量（最后一行预留10）。
3. 最终查询：
   • 无扣除需求时直接返回原qty1
   • 最后一行最多扣至10，剩余扣除量从前面的行按顺序抵扣
   • 非最后一行按累计可用量计算应扣除的部分，直到总扣除量扣完

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