我在sql服务器中有一个临时表,如下所示
╔═══════╦═════════╦══════╦═════════╗ ║Serial ║ Account ║ Due ║ Balance ║ ║1 ║ A1 ║ 1000 ║ 3100 ║ ║2 ║ A1 ║ 1000 ║ 3100 ║ ║3 ║ A1 ║ 1000 ║ 3100 ║ ║4 ║ A1 ║ 1000 ║ 3100 ║ ║1 ║ A2 ║ 100 ║ 3100 ║ ║2 ║ A2 ║ 100 ║ 3100 ║ ║1 ║ B1 ║ 1000 ║ 1100 ║ ║2 ║ B1 ║ 1000 ║ 1100 ║ ║1 ║ B2 ║ 100 ║ 1100 ║ ║2 ║ B2 ║ 100 ║ 1100 ║ ╚═══════╩═════════╩══════╩═════════╝
我想确定将收集到的行. A1和A2 Due将从3100收取,B1和B2将从1100收集.
首先我使用累积的Due如下
╔═══════╔═════════╦══════╦════════════╦═════════╦ ║Serial ║ Account ║ Due ║ Cumulative ║ Balance ║ ║1 ║ A1 ║ 1000 ║ 1000 ║ 3100 ║ ║2 ║ A1 ║ 1000 ║ 2000 ║ 3100 ║ ║3 ║ A1 ║ 1000 ║ 3000 ║ 3100 ║ ║4 ║ A1 ║ 1000 ║ 4000 ║ 3100 ║ ║1 ║ A2 ║ 100 ║ 100 ║ 3100 ║ ║2 ║ A2 ║ 100 ║ 200 ║ 3100 ║ ║1 ║ B1 ║ 1000 ║ 1000 ║ 1100 ║ ║2 ║ B1 ║ 1000 ║ 2000 ║ 1100 ║ ║1 ║ B2 ║ 100 ║ 100 ║ 1100 ║ ║2 ║ B2 ║ 100 ║ 200 ║ 1100 ║ ╚═══════╚═════════╩══════╩════════════╩═════════╝
现在我想选择以下行作为输出
╔═══════╔═════════╦══════╦════════════╦═════════╦ ║Serial ║ Account ║ Due ║ Cumulative ║ Balance ║ ║1 ║ A1 ║ 1000 ║ 1000 ║ 3100 ║ ║2 ║ A1 ║ 1000 ║ 2000 ║ 3100 ║ ║3 ║ A1 ║ 1000 ║ 3000 ║ 3100 ║ ║1 ║ A2 ║ 100 ║ 100 ║ 3100 ║ ║1 ║ B1 ║ 1000 ║ 1000 ║ 1100 ║ ║1 ║ B2 ║ 100 ║ 100 ║ 1100 ║ ╚═══════╚═════════╩══════╩════════════╩═════════╩
这是我被困的地方.如何在不使用游标或循环的情况下选择这些行.所有我想用select语句和窗口函数来做这件事.
谢谢.
╔═══════╔═════════╦══════╦═══════════════════╦ ║Serial ║ Account ║ Due ║ Balance Remaining ║ ║1 ║ A1 ║ 1000 ║ 3100 ║ ║2 ║ A1 ║ 1000 ║ 2100 ║ ║3 ║ A1 ║ 1000 ║ 1100 ║ ║4 ║ A1 ║ 1000 ║ 100 ║ ║1 ║ A2 ║ 100 ║ 100 ║ ║2 ║ A2 ║ 100 ║ 0 ║ ║1 ║ B1 ║ 1000 ║ 1100 ║ ║2 ║ B1 ║ 1000 ║ 100 ║ ║1 ║ B2 ║ 100 ║ 100 ║ ║2 ║ B2 ║ 100 ║ 0 ║ ╚═══════╚═════════╩══════╩═══════════════════╩
余额余额等于/大于到期我们用差异更新它,否则它将保持原样.问题在于通过在A&之间划分来更新行. B.
UPDATE
我提供了与新数据集的链接,以更清楚地表达我的要求.
new dataset
解决方法
最后,用更新查询解决了这个问题.
UPDATE A
SET
A.Balance = @Balance,@PreBalance = @Balance,@Balance = ( CASE WHEN (@Balance IS NULL OR @AccountType <> A.AccountType)
THEN
CASE WHEN A.Balance - A.Due >= 0
THEN A.Balance
ELSE A.Balance + A.Due
END
ELSE
CASE WHEN @Balance - A.Due >= 0 AND (@Flag = 1 OR @AccountNO <> A.AccountNO)
THEN @Balance
ELSE @Balance + A.Due
END
END) - A.Due,A.FLAG = @Flag,@AccountNO = CASE WHEN A.Flag = 0 THEN A.AccountNO ELSE 'NoDueFoundForAcc' END,@Flag = CASE WHEN @AccountType = A.AccountType
THEN
CASE WHEN @PreBalance = @Balance
THEN 0
ELSE 1
END
ELSE
CASE WHEN A.Balance - A.Due >= 0
THEN 1
ELSE 0
END
END,@AccountType = A.AccountType
FROM #tempTable A
SELECT * FROM #tempTable A WHERE A.Flag = 1
