优化计算账户余额的 SQL 查询

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

Tables:

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

我需要使用上述两个表的账户持有人姓名、账户号码和当前余额。

下面是我的查询，我想获得优化的查询，即如果可能的话不使用子查询。Note:就我而言，贷方 = 添加到帐户的金额，借方 = 从帐户中扣除的金额。

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

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

Thanks,

Paresh J

这个想法是首先生成每种交易类型的总和，Debit and 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