将金额分配到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 

Answer 2

如果预算小于薪水

 CASE
             --run out
             WHEN prev_running_total >= @Budget THEN 0
             WHEN @Budget <= T2.Salary  THEN @Budget  
             --budget left but not enough for whole salary
             WHEN running_total > @Budget THEN @Budget - prev_running_total 
             --Can do full amount 
             ELSE Salary
         END

Answer 3

   -- FOR EARLIER VERSIONS OF SQL SERVER (CTE SUPPORTED)
   DECLARE @Budget INT = 905;
   IF OBJECT_ID ('TEMPDB..#emp') IS NOT NULL
   DROP TABLE #EMP
   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);
   WITH T1 AS
   ( SELECT  A.* ,
             running_total = SUM(B.Salary) -- OVER (ORDER BY [Rank] )
                                               --ROWS BETWEEN UNBOUNDED PRECEDING 
                                               --AND CURRENT ROW
                                            --)
            FROM #emp A
            LEFT JOIN #emp B ON B.[Rank] <= A.[Rank]
            GROUP BY A.Employee, A.[Rank], A.Salary
            --ORDER BY 1,2
    )
   , 
   T2 AS 
   (
    SELECT A.*, prev_running_total = B.running_total
    FROM T1 A 
    LEFT JOIN T1 B ON B.[Rank] + 1 = A.[Rank]
   )
   SELECT   Employee,
            Rank,
            Salary,
            CASE
                --run out
                WHEN isnull(prev_running_total,0) >= @Budget THEN 0
                --budget left but not enough for whole salary
                WHEN running_total > @Budget THEN @Budget - isnull(prev_running_total, 0)
                --Can do full amount 
                ELSE Salary
            END Distribution 
   FROM     T2
   ORDER BY 2;
   IF OBJECT_ID ('TEMPDB..#emp') IS NOT NULL
   DROP TABLE #EMP