MySQL视图中按行递减扣除固定值的SQL实现需求

需求背景 #

现有视图结构如下：

需要实现：从首行的rest列开始累计扣除固定值40，新增rest-40列记录每行实际扣除数值，原rest列显示扣除后的剩余值（剩余为负则设为0）；若首行扣除后仍有未完成的扣除量，继续从下一行扣除，直至40全部扣完为止。禁止使用UPDATE语句，最终期望结果如下：

问题分析 #

你之前尝试的代码仅实现了每行单独扣除40的逻辑，未处理累计扣除、逐行递减的核心需求。需要通过窗口函数计算累计rest值，以此判断每行需要承担的扣除量。

解决方案代码 #

假设视图名为your_view，使用CTE结合窗口函数实现需求：

WITH running_totals AS (
    SELECT 
        concatenated_dates,
        mat_emp,
        rest,
        -- 计算当前行及之前所有行的rest累计和
        SUM(rest) OVER (ORDER BY concatenated_dates) AS cumulative_rest,
        -- 计算当前行之前所有行的rest累计和（首行返回NULL）
        SUM(rest) OVER (ORDER BY concatenated_dates ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_cumulative_rest
    FROM your_view
)
SELECT
    -- 计算当前行实际扣除的数值
    CASE
        WHEN COALESCE(prev_cumulative_rest, 0) >= 40 THEN 0
        WHEN cumulative_rest <= 40 THEN rest
        ELSE 40 - COALESCE(prev_cumulative_rest, 0)
    END AS "rest-40",
    concatenated_dates AS concatenated,
    mat_emp,
    -- 计算扣除后的剩余rest值
    CASE
        WHEN COALESCE(prev_cumulative_rest, 0) >= 40 THEN rest
        WHEN cumulative_rest <= 40 THEN 0
        ELSE rest - (40 - COALESCE(prev_cumulative_rest, 0))
    END AS rest
FROM running_totals
-- 仅保留需要参与扣除的行（匹配期望结果）
WHERE COALESCE(prev_cumulative_rest, 0) < 40

代码解释 #

1. CTE running_totals：

   • 通过窗口函数计算每行的累计rest值（cumulative_rest），以及当前行之前所有行的累计rest值（prev_cumulative_rest），用于判断扣除进度。
   • 使用COALESCE处理首行prev_cumulative_rest为NULL的情况，将其转为0。

2. 扣除逻辑判断：

   • rest-40列：根据累计进度，判断当前行需要承担的扣除量——若之前累计已达标则扣0；若当前累计未达标则全扣；否则扣剩余未完成的部分。
   • rest列：根据实际扣除量计算剩余值——若无需扣除则保留原值；若全扣则设为0；否则计算剩余量。

3. 过滤条件：

   • 最后通过WHERE子句过滤掉无需参与扣除的行，完全匹配你给出的期望结果。若需要显示所有行（包括未被扣除的行），可去掉该条件。

注意事项 #

• 确保ORDER BY concatenated_dates能正确排序，保证首行是EX 2018/2019；若排序规则需要调整，修改ORDER BY子句即可。
• 全程使用查询计算，未修改视图数据，符合不能使用UPDATE的要求。

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