基于Accounts表汇总Account Details数据的SQL查询需求

现有数据库表 #

1. Accounts表 #

包含Accountname、accountCode字段，数据如下：

2. Account Details表 #

包含AccountCode、Debit、Credit字段，数据如下：

查询需求 #

需要编写SQL语句，生成包含AccountName、TotalDebit、TotalCredit、CumulativeBalance字段的结果表，预期结果如下：

当前尝试的问题 #

当前编写的SQL仅能显示AccountCode，无法满足需求，代码如下：

select
    sum(Debit) as TotalDebit,
    sum(Credit) as TotalCredit,
    Account_Code as AC
from AccountDetails as A 
inner join Accounts as B ON A.AccountCode = B.AccountCode
where AccountCode like N'11%'
group by A.AccountCode

修正后的SQL语句 #

SELECT
    B.Accountname AS AccountName,
    SUM(A.Debit) AS TotalDebit,
    SUM(A.Credit) AS TotalCredit,
    SUM(SUM(A.Debit) - SUM(A.Credit)) OVER (ORDER BY B.AccountCode) AS CumulativeBalance
FROM AccountDetails AS A
INNER JOIN Accounts AS B ON A.AccountCode = B.AccountCode
WHERE A.AccountCode LIKE N'11%'
GROUP BY B.Accountname, B.AccountCode
ORDER BY B.AccountCode;

关键说明 #

1. 需在SELECT中引入B.Accountname，并将其与B.AccountCode一同加入GROUP BY子句，确保分组维度包含账户名称
2. 利用窗口函数SUM() OVER (ORDER BY B.AccountCode)实现累计余额计算：先算出单个账户的余额=总借方-总贷方，再按账户编码顺序累加得到累计值
3. 保留WHERE条件，过滤出编码以'11'开头的目标账户

内容的提问来源于stack exchange，提问作者Issam