sql - 有条件地总结 - 两个查询合二为一

Question

我正在尝试计算我所有帐户的余额。一个帐户可以与许多交易相关。一笔交易与两个账户相关联。一个帐户是要借记的帐户transaction.debitaccountid。而另一个帐户将被记入transaction.creditaccountid。

一个事务可以由 1:many TransactionLines 组成。这允许在不同的类别或预算之间拆分交易。

所以我的结构：

（我完全控制了结构，所以可以改变）

我正在尝试获取所有帐户及其余额的列表。稍后我将在 linq 中执行此操作（ef 核心）。但目前，只是试图找到最有效的方法。

我被困在一个有两个查询的联合上，我认为这不是很好。

;WITH cte AS (
Select a.ExternalId AS ExternalId, a.Name, SUM(tl.Amount) AS Amount, 1 AS Credit
FROM Account a
INNER JOIN [Transaction] t
ON t.CreditAccountId = a.Id 
INNER JOIN TransactionLine tl
ON tl.TransactionId = t.id
WHERE a.AccountTypeId = 1
GROUP BY a.Id, a.ExternalId, a.Name

UNION

Select a.ExternalId AS ExternalId, a.Name, SUM(tl.Amount) AS Amount, -1 AS Credit
FROM Account a
INNER JOIN [Transaction] t
ON t.DebitAccountId = a.Id 
INNER JOIN TransactionLine tl
ON tl.TransactionId = t.id
WHERE a.AccountTypeId = 1
GROUP BY a.Id, a.ExternalId, a.Name
)
SELECT ExternalId, Name, SUM(Amount * Credit) FROM cte
GROUP BY ExternalId, Name

我得到了正确的答案，但它似乎效率低下。另外，我需要在链接中执行此操作，因此我的应用程序将有两个查询，然后在 linq 中处理结果。

有没有办法在单个 SQL 查询中做到这一点？也许加入与 OR 的交易？但是，不知道如何计算它是借方还是贷方价值。

也许我需要将金额存储在交易表中？但这可能会出错。可能更容易并且可以变得安全？

编辑：这几乎可行。但我无法正确分组：

Select a.ExternalId AS ExternalId, a.Name, 
SUM(tl.Amount) *  
case when t.CreditAccountId = a.Id then 1 
when t.DebitAccountId = a.Id then -1 end
as Amt 
FROM Account a
INNER JOIN [Transaction] t
ON (t.CreditAccountId = a.Id OR t.DebitAccountId = a.Id )
INNER JOIN TransactionLine tl
ON tl.TransactionId = t.id
WHERE a.AccountTypeId = 1
GROUP BY a.Id, a.ExternalId, a.Name, t.CreditAccountId, t.DebitAccountId

导致：

这些需要按 ExternalId 和 Name 分组，并加上 Amount。

编辑2：修复它。

Select a.ExternalId AS ExternalId, a.Name, 
SUM(tl.Amount *  
case when t.CreditAccountId = a.Id then 1 
when t.DebitAccountId = a.Id then -1 end)
as Amt 
FROM Account a
INNER JOIN [Transaction] t
ON (t.CreditAccountId = a.Id OR t.DebitAccountId = a.Id )
INNER JOIN TransactionLine tl
ON tl.TransactionId = t.id
WHERE a.AccountTypeId = 1
GROUP BY a.Id, a.ExternalId, a.Name--, t.CreditAccountId, t.DebitAccountId

结果：

标签： sqlsql-server

Answer 1

希望，我正确理解了你的问题。

我认为您可以使用ORandCASE语句在同一个查询中实现结果。请检查以下查询是否有帮助。

Select a.ExternalId AS ExternalId, a.Name, 
SUM(tl.Amount *  
case when t.CreditAccountId = a.Id then 1 
when t.DebitAccountId = a.Id then -1 end)
as Amt 
FROM Account a
INNER JOIN [Transaction] t
ON (t.CreditAccountId = a.Id OR t.DebitAccountId = a.Id )
INNER JOIN TransactionLine tl
ON tl.TransactionId = t.id
WHERE a.AccountTypeId = 1
GROUP BY a.Id, a.ExternalId, a.Name