SQL 从行中减去变量

Question

有没有办法减去一个变量并将该变量的剩余部分用于 MS-SQL 中的下一列

基本上我想要做的是减去某个值并开始按顺序播放 var1-3 中的值

之前：

ID|var1|var2|var3
 1| 500| 100| 200
 2| 800| 400| 300
 3|1200|3200|2400

示例 1 - 减去 600 我会得到

ID|var1|var2|var3
 1|   0|   0| 200
 2| 800| 400| 300
 3|1200|3200|2400

样本 2 - 减去 750 我会得到

ID|var1|var2|var3
 1|   0|   0|  50
 2| 800| 400| 300
 3|1200|3200|2400

样本 3 - 减去 900 我会得到

ID|var1|var2|var3
 1|   0|   0|   0
 2| 700| 400| 300
 3|1200|3200|2400

带有“0”的字段可以为“空”。

这是我尝试过的，但非常笨重：|

DECLARE @Amount DECIMAL(18,2) = 900

CREATE TABLE #table(ID [int] NULL, var1 decimal(18,2),  var2 decimal(18,2), var3 decimal(18,2))
INSERT INTO #table (ID,var1,var2,var3) VALUES (1,500,100,200)
INSERT INTO #table (ID,var1,var2,var3) VALUES (2,800,400,300)
INSERT INTO #table (ID,var1,var2,var3) VALUES (3,1200,3200,2400)
DECLARE @CurrentRow INT
DECLARE @OrgAmount DECIMAL(18,2)

WHILE (SELECT SUM( ISNULL(var1,0)) + SUM( ISNULL(var2,0)) + SUM( ISNULL(var3,0)) FROM #table) != 0 AND @Amount != 0
BEGIN
    SELECT TOP 1 @CurrentRow = ID FROM #table
    WHERE var1 != 0 OR var2 != 0 OR  var3 != 0
    ORDER BY ID ASC
    
    UPDATE #table
    SET var1 = CASE WHEN var1 - @Amount < 0 THEN 0 ELSE var1 - @Amount END,
    @OrgAmount = var1
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END
    
    
    UPDATE #table
    SET var2 = CASE WHEN var2 - @Amount < 0 THEN 0 ELSE var2 - @Amount END,
    @OrgAmount = var2
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END

    UPDATE #table
    SET var3 = CASE WHEN var3 - @Amount < 0 THEN 0 ELSE var3 - @Amount END,
    @OrgAmount = var3
    WHERE @CurrentRow = ID
    SET @Amount = CASE WHEN @Amount - @OrgAmount < 0 THEN 0 ELSE @Amount - @OrgAmount END

END

SELECT * FROM #table

DROP TABLE #table

感谢您的协助。

Answer 1

虽然我同意评论，这可能可以更好地布局，但以下 sql 显示了解决该问题的一种方法：

declare @subtract int
set @subtract = 900

;WITH CTE_Data as (
    select id=1, var1= 500, var2 = 100, var3 = 200
    union select id=2, var1= 800, var2 = 400, var3 = 300
    union select id=3, var1= 1200, var2 = 3200, var3 = 2400
),
CTE_RunningTotals as (
    select 
        id, 
        pretot = 0, 
        var1, var2, var3,       
        tot1 = var1, 
        tot2 = var1 + var2, 
        tot3 = var1 + var2 + var3
    from CTE_Data where id = 1  
    union all
    select 
        d.id, 
        pretot = rt.tot3,
        d.var1, d.var2, d.var3,     
        tot1 = d.var1 + rt.tot3,
        tot2 = d.var1 + d.var2 + rt.tot3,
        tot3 = d.var1 + d.var2 + d.var3 + rt.tot3
    from
        CTE_Data d
        join CTE_RunningTotals rt on d.id = rt.id+1
    where d.id > 1
)
select 
    id,
    var1 = case when pretot - @subtract >= 0 then var1 
            else case when tot1 - @subtract > 0 then tot1 - @subtract else 0 end
            end,
    var2 = case when tot1 - @subtract >= 0 then var2
            else case when tot2 - @subtract > 0 then tot2 - @subtract else 0 end
            end,
    var3 = case when tot2 - @subtract >= 0 then var3 
            else case when tot3 - @subtract >0 then tot3 - @subtract else 0 end
            end
from CTE_RunningTotals

返回

id    var1    var2    var3
1     0       0       0
2     700     400     300
3     1200    3200    2400

CTE_Data 只是输入数据，您可以为源表更改它。

CTE_RunningTotals 包含原始数据，以及表中直到当前行/列的所有值的运行总计。

如果前一行/列为零（即相减值大于到目前为止的运行总计），则从当前值中取出相减值，否则返回数据值。

如果您在 CTE 声明之后select * from CTE_RunningTotals，您可能会发现更容易弄清楚发生了什么，这样您就可以看到工作情况。