带条件的累积求和SQL实现需求（附示例数据表）

现有表MainTblcnt（结构及测试数据如下），需实现带条件的累积扣除逻辑：给定初始值subval=16，逐行与表中mainval计算：

• 当subval > mainval时，扣除当前行的全部mainval，并将subval更新为subval - mainval，用于下一行计算
• 当subval <= mainval时，扣除剩余的全部subval，subval置为0，后续行不再扣除

最终生成包含每一行处理结果的数据集。

表结构与测试数据 #

CREATE TABLE [dbo].[MainTblcnt](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [mainid] [int] NULL,
    [mainval] [int] NULL,
CONSTRAINT [PK_MainTblcnt] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[MainTblcnt] ON 
GO

INSERT [dbo].[MainTblcnt] ([id], [mainid], [mainval])
VALUES
(1, 477, 20),
(2, 477, 20),
(3, 477, 6),
(4, 477, 5),
(5, 477, 8),
(6, 477, 3),
(7, 477, 2),
(8, 477, 10)
GO

SET IDENTITY_INSERT [dbo].[MainTblcnt] OFF
GO

实现SQL语句 #

使用递归CTE逐行迭代处理subval的更新逻辑，代码如下：

WITH RecursiveCTE AS (
    -- 初始化：处理第一行
    SELECT 
        id,
        mainid,
        mainval,
        CASE WHEN 16 > mainval THEN mainval ELSE 16 END AS deducted_val,
        CASE WHEN 16 > mainval THEN 16 - mainval ELSE 0 END AS remaining_subval
    FROM MainTblcnt
    WHERE id = 1

    UNION ALL

    -- 递归处理后续行
    SELECT 
        m.id,
        m.mainid,
        m.mainval,
        -- 根据剩余subval判断当前行扣除量
        CASE WHEN r.remaining_subval > m.mainval THEN m.mainval 
             WHEN r.remaining_subval > 0 THEN r.remaining_subval 
             ELSE 0 END AS deducted_val,
        -- 更新剩余subval
        CASE WHEN r.remaining_subval > m.mainval THEN r.remaining_subval - m.mainval 
             ELSE 0 END AS remaining_subval
    FROM MainTblcnt m
    INNER JOIN RecursiveCTE r ON m.id = r.id + 1
)
SELECT 
    id,
    mainid,
    mainval,
    deducted_val,
    remaining_subval
FROM RecursiveCTE
ORDER BY id;

逻辑说明 #

1. 初始化部分先处理第一行，基于初始subval=16计算该行的扣除量和剩余subval
2. 递归部分逐行关联上一行结果，根据剩余subval判断当前行的扣除规则：
   • 剩余subval大于当前mainval：扣除全部mainval，剩余值更新为subval - mainval
   • 剩余subval大于0但小于等于mainval：扣除剩余全部subval，剩余值置为0
   • 剩余subval已为0：当前行扣除量为0，剩余值保持0

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