SQL Server的复杂选择查询
我在下面的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将从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 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
很简单
select * from account
where (Balance-(Select sum(ac.Due) from account ac where
ac.SerialNo<=account.SerialNo and ac.Account =account.Account )>0)
更新
A1和A2之间没有关系，表示余额3100将被分配在A1和A2之间，而不是B1。
所以你必须指定一些a1和a2在同一组的地方
有建议您选择
在表格中添加没有组的列，并给A1和A2赋予相同的否，对于B1和B2则赋予相同的否。 然后添加指定A1应优先扣除的优先级列，然后如果剩余a2的余额将有机会
然后查询将是
SELECT *
FROM account
WHERE
( Balance - ( SELECT
SUM(ac.Due)
FROM
account ac
WHERE
( ac.GroupNo = account.GroupNo
AND ( ( ac.Account = account.Account
AND ( ac.SerialNo <= account.SerialNo )
)
OR ac.Prioirty < account.Prioirty
)
)
) > 0 )
为了得到这个输出，你可以使用如下的简单查询：
╔═══════╔═════════╦══════╦════════════╦═════════╦
║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,Cumulative,Balance from
(Select *, row_number() over(Partition by Account, order by serial number desc) as r from Temp) t
where t.r>1