时间戳

首页 > 解决方案 > T-SQL-从 T2 更新 T1 Balance+Amt-T2 中的多行匹配 T1-Amt 上有大小写逻辑

tsql - T-SQL-从 T2 更新 T1 Balance+Amt-T2 中的多行匹配 T1-Amt 上有大小写逻辑

问题描述

这可能很简单，我很想念它——我很想使用光标——但似乎太简单了，不得不走那条路——希望这里有人能提供帮助。

这是我的代码和示例数据：

drop table if exists Customer
create table Customer (
ID int not null,
[Name] nvarchar(20),
Balance money not null
)

drop table if exists Transact
create table Transact (
ID int not null,
CustomerID int not null,
[Type] int not null,  -- 1 = payment, 2 = invoice
Amount money not null
)

insert into Customer
    Values (1, 'Bob', 100), (2, 'John', 500)

insert into Transact
    Values (1, 1, 2, 50), (2, 1, 1, 25), (3, 1, 2, 10),
    (4, 2, 2, 100), (5, 2, 1, 50), (6, 2, 1, 200)

select * from Customer
select * from Transact

这给出了这个结果：

ID  Name    Balance
1   Bob     100.00
2   John    500.00

和

ID  CustomerID  Type    Amount
1   1           2       50.00
2   1           1       25.00
3   1           2       10.00
4   2           2       100.00
5   2           1       50.00
6   2           1       200.00

我想要做的是将交易表中的金额值添加到客户表的余额列中。这是我试过的代码：

update Customer
set Customer.Balance = Customer.Balance + t.Amount
from Transact t
where Customer.ID = t.CustomerID

但这给出了这个结果 - 不是我想要的：

客户表：

ID  Name    Balance
1   Bob     150.00
2   John    600.00

我试图得到这个结果：

ID  Name    Balance
1   Bob     135.00
2   John    350.00

我假设在更新中客户余额是固定的，即使它遍历了所有匹配的交易记录——基本上最后一个获胜。

提前致谢！

PS - 要在此处添加更多信息，此游标方法可以满足我的要求，但我正在尝试使用 Update 而不是游标找到基于集合的方法。但要显示所需的逻辑和结果：

Declare @CustomerID int
Declare @TransType int
Declare @Amount money
declare trans_cursor cursor for
    select  t.CustomerID, t.[Type], T.Amount
    from    Transact t
open trans_cursor
fetch next from trans_cursor into @CustomerID, @TransType, @Amount
while @@FETCH_STATUS = 0
    begin
        Update C
        SET C.Balance = C.Balance +
            (case when @TransType = 2 then @Amount
            when @TransType = 1 then (-(@Amount))
        end)
        from Customer C
        where C.ID = @CustomerID
        fetch next from trans_cursor into @CustomerID, @TransType, @Amount
    end
close trans_cursor
deallocate trans_cursor

这给出了预期的结果：

ID  Name    Balance
1   Bob     135.00
2   John    350.00

再次感谢！

标签： tsqlsql-update

解决方案

UPDATE c
SET c.Balance = c.Balance + t.Amount
FROM Customer AS c
CROSS APPLY (
    SELECT SUM(CASE WHEN Type = 2 THEN Amount ELSE Amount * -1 END) AS Amount FROM Transact
    WHERE CustomerID = c.ID
    GROUP BY CustomerID 
) t

推荐阅读