sql - 更新表时将值分配给所有行

Question

我有像tblCustData这样的表结构

ID    UserID    Fee FeePaid
 1      12      150   0
 2      12      100   0
 3      12      50    0

并且要在 FeePaid 列中更新值，这样如果我在 200 中的 @Amt 变量中有值，那么它应该更新任意两行

输出应该类似于

ID    UserID    Fee FeePaid
1      12       150   150
2      12       100   50
3      12       50    0

FeePaid 不应大于 Fee 列，但是如果我在 @Amt 变量中传递 350 ，它应该产生类似的输出

ID    UserID    Fee FeePaid
1       12      150   200
2       12      100   100
3       12      50    50

仅当@Amt超过费用列中的总值

我无法思考超出这个查询的内容

Update tblCustData
Set FeePaid=@Amt
Where UserID=12

Answer 1

首先使用 CTE 语法，我们准备一个具有总和分布的表，然后使用唯一字段 Code 使用 CASE 更新主表以处理所有可能的方式(包括第一行和余数)。

Declare @Amt int;
SET @Amt=250;


with T as 
(
   SELECT ROW_NUMBER() OVER (ORDER BY Fee desc) as rn, *
   FROM tblCustData WHERE UserId=12
)  
,T2 as 
(
   SELECT *,
          ISNULL((SELECT SUM(Fee-FeePaid) FROM T WHERE T1.RN<RN),0) as PrevSum 
   FROM T as T1
 )

UPDATE
    A
SET A.FeePaid = A.FeePaid+ CASE WHEN (B.PrevSum+B.Fee-B.FeePaid<=@Amt) 
                                     AND (B.RN<>1) 
                                        THEN B.Fee-B.FeePaid
                     WHEN (B.PrevSum+B.Fee-B.FeePaid<=@Amt) AND (B.RN=1) 
                                        THEN @Amt-B.PrevSum
                     WHEN B.PrevSum>=@Amt 
                                        THEN 0                 
                     WHEN B.PrevSum+B.Fee-B.FeePaid>@Amt 
                                        THEN @Amt-B.PrevSum
              END
FROM
    tblCustData A
    JOIN T2 B ON A.Code = B.Code
GO

SQLFiddle demo