优化SQL查询以计算帐户余额

Question

我有以下表格，分别是BankDetails和Transactiondetails。使用这两个表，我想获得帐户名称的当前余额。

表格：

Create table Bankdetails
(
AccName varchar(50),
AccNo int,
OpBal numeric(18,2)
)

Create table Trandetails
(
AccNo int,
Amount numeric(18,2),
Trantype varchar(10)
)

插入两个表的脚本：

insert into Bankdetails values('A', 12345, 30000.00)
insert into Bankdetails values('B', 13345, 30000.00)
insert into Bankdetails values('C', 14545, 30000.00)
insert into Bankdetails values('D', 15045, 30000.00)

insert into Trandetails values(12345, 5000.00, 'Credit')
insert into Trandetails values(13345, 5000.00, 'Debit')
insert into Trandetails values(15045, 5000.00, 'Debit')
insert into Trandetails values(13345, 5000.00, 'Credit')
insert into Trandetails values(12345, 5000.00, 'Debit')
insert into Trandetails values(13345, 5000.00, 'Debit')
insert into Trandetails values(14545, 5000.00, 'Credit')
insert into Trandetails values(15045, 5000.00, 'Debit')
insert into Trandetails values(14545, 5000.00, 'Debit')

输出将是这样的：

AccName Accno    CurrBal
  A     12345   30000.00 
  B     13345   25000.00
  C     14545   30000.00
  D     15045   20000.00

我需要使用以上两个表格输入帐户持有人姓名，帐户编号和当前余额。

下面是我的查询，我想获得优化的查询，即如果可能的话不使用子查询。 注意： 在我的情况下，贷方=添加到帐户中的金额，借方=从帐户中扣除的金额。

Select bd.accname, bd.accno, 
(bd.opbal - isnull((select SUM(Amount) from Trandetails where Trantype = 'Debit' and accno = bd.accno group by accno),0) + isnull((select SUM(Amount) from Trandetails where Trantype = 'Credit'  and accno = bd.accno group by accno),0)) as Bal
From Bankdetails BD inner join Trandetails TD on td.AccNo = bd.AccNo
group by bd.accno, bd.accname, bd.opbal

对于未遵循表的正确命名约定，我深表歉意。任何帮助将不胜感激。

谢谢，

Paresh J

Answer 1

这个想法是首先为每种交易类型生成Debit和Credit。这样，将其加入Bankdetails即可计算当前余额。

;with cte as(
    select
        AccNo,
        Credit = sum(case when TranType = 'Credit' then Amount else 0 end),
        Debit = sum(case when TranType = 'Debit' then Amount else 0 end)
    from Trandetails
    group by
        AccNo
)
select
    bd.AccName,
    bd.AccNo,
    CurrBal = bd.opBal - c.Debit + c.Credit
from BankDetails bd
inner join cte c
    on c.Accno = bd.Accno