Oracle SQL实现特定逻辑动态求和：LBKUM仅首次参与计算

需求说明 #

需要生成名为Output的计算列，规则如下：

• 按LOGSYS、MATNR、PLANT分组后，每组第一行取值为 LBKUM + WEEKLY_QTY
• 从第二行开始，取值为上一行的Output值 + 当前行WEEKLY_QTY

示例数据 #

解决方案 #

方法1：窗口函数（推荐，简洁高效） #

利用Oracle的窗口累计求和函数，直接实现需求逻辑，本质是LBKUM加上分组内从第一行到当前行的WEEKLY_QTY累计和：

SELECT 
    LOGSYS,
    MATNR,
    PLANT,
    LBKUM,
    QTY_WITHDRAWL,
    PLUS_QTY,
    WEEK,
    WEEKLY_QTY,
    LBKUM + SUM(WEEKLY_QTY) OVER (
        PARTITION BY LOGSYS, MATNR, PLANT 
        ORDER BY WEEK 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Output
FROM your_table_name
ORDER BY LOGSYS, MATNR, PLANT, WEEK;

• PARTITION BY：确保累计计算在同一物料、工厂、系统分组内进行
• ORDER BY WEEK：指定按周排序，保证累计顺序符合业务逻辑
• SUM(...) OVER (...)：计算分组内从首行到当前行的WEEKLY_QTY累计和，叠加初始值LBKUM得到目标列

方法2：递归CTE（兼容旧版本Oracle） #

如果使用的Oracle版本不支持窗口函数，可通过递归方式逐行计算：

WITH ranked_data AS (
    SELECT 
        LOGSYS,
        MATNR,
        PLANT,
        LBKUM,
        QTY_WITHDRAWL,
        PLUS_QTY,
        WEEK,
        WEEKLY_QTY,
        ROW_NUMBER() OVER (PARTITION BY LOGSYS, MATNR, PLANT ORDER BY WEEK) AS rn
    FROM your_table_name
),
recursive_output AS (
    SELECT 
        LOGSYS,
        MATNR,
        PLANT,
        LBKUM,
        QTY_WITHDRAWL,
        PLUS_QTY,
        WEEK,
        WEEKLY_QTY,
        LBKUM + WEEKLY_QTY AS Output,
        rn
    FROM ranked_data
    WHERE rn = 1
    UNION ALL
    SELECT 
        rd.LOGSYS,
        rd.MATNR,
        rd.PLANT,
        rd.LBKUM,
        rd.QTY_WITHDRAWL,
        rd.PLUS_QTY,
        rd.WEEK,
        rd.WEEKLY_QTY,
        ro.Output + rd.WEEKLY_QTY AS Output,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_output ro 
        ON rd.LOGSYS = ro.LOGSYS 
        AND rd.MATNR = ro.MATNR 
        AND rd.PLANT = ro.PLANT 
        AND rd.rn = ro.rn + 1
)
SELECT 
    LOGSYS,
    MATNR,
    PLANT,
    LBKUM,
    QTY_WITHDRAWL,
    PLUS_QTY,
    WEEK,
    WEEKLY_QTY,
    Output
FROM recursive_output
ORDER BY LOGSYS, MATNR, PLANT, WEEK;

• 第一步：给每行分配分组内的行号，确定计算顺序
• 第二步：递归计算，首行直接用LBKUM + WEEKLY_QTY，后续行基于上一行的结果叠加当前WEEKLY_QTY

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