sql-server - 在 SQL Server 中将金额分布到多行

Question

我有任意金额可以花，比如说 1000 美元

我也有几十行，比方说员工的薪水作为一列

我如何按优先级顺序将 1000 美元的预算分配给员工，以便他们每个人都能获得薪水列中的值，直到钱花光为止？一旦预算全部用完，其余员工将剩下零。

Employee, Rank, Salary
John, 1, 500$
Anne, 2, 400$
Rob, 3, 300$
Bill, 4, 200$

结果应该是:

John, 1, 500$, 500$
Anne, 2, 400$, 400$
Rob, 3, 300$, 100$    --Only 100 left in the budget
Bill, 4, 200$, 0$

知道如何在没有光标的情况下做到这一点吗？

Answer 1

这是一种方法。

CREATE TABLE #emp (Employee VARCHAR(10), Rank INT, Salary INT CHECK (Salary > 0));

INSERT INTO #emp
VALUES      ('John',1,500),
            ('Anne',2,400),
            ('Rob',3,300 ),
            ('Bill',4,200);

DECLARE @Budget INT = 1000;

WITH T1 AS
( SELECT  * ,
          running_total = SUM(Salary) OVER (ORDER BY Rank 
                                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                            AND CURRENT ROW)
         FROM #emp ), 
T2 AS 
(
SELECT *, 
      prev_running_total = LAG(running_total) OVER (ORDER BY Rank)
FROM T1
)
SELECT   Employee,
         Rank,
         Salary,
         CASE
             --run out
             WHEN prev_running_total >= @Budget THEN 0
             --budget left but not enough for whole salary
             WHEN running_total > @Budget THEN @Budget - prev_running_total 
             --Can do full amount 
             ELSE Salary
         END
FROM     T2;

DROP TABLE #emp