如何用SQL按AccCode前缀关联两表计算账户汇总金额
原SQL的问题根源
- 语法层面的硬错:LIKE匹配时没有将外层查询的AccCode字段值和
%通配符拼接成完整匹配规则,数据库会把AccCode LIKE xacc.AccCode%识别为找AccCode等于固定字符串"xacc.AccCode%"的记录,完全触发不了前缀匹配逻辑,这也是你处理长AccCode(比如12)时结果不对的核心原因 - 逻辑漏洞:原写法会无差别累加同前缀下所有账户的金额,没有按账户类型过滤,只要Main账户在tblamounts里有冗余录入的金额,就会出现重复计算
修正后的实现代码
适配现有AccCode前缀规则的版本
按照你说的编码逻辑(每一层账户的编码比上一级多1位,所有实际发生的金额都只存在于Sub类型账户上),修正匹配条件即可:
SELECT xacc.AccCode, xacc.AccType, ( SELECT SUM(ta.TheAmount) FROM tblamounts ta JOIN tblaccounts sub_acc ON ta.AccID = sub_acc.AccID WHERE -- 只统计Sub账户的实际发生额,避免Main账户的冗余数据干扰 sub_acc.AccType = 'Sub' -- 正确拼接通配符,匹配所有同前缀的下属账户 sub_acc.AccCode LIKE CONCAT(xacc.AccCode, '%') ) AS TotalAmount FROM tblaccounts xacc
跨数据库适配提示:不同数据库的字符串拼接语法有差异,SQL Server可将
CONCAT(xacc.AccCode, '%')替换为xacc.AccCode + '%';PostgreSQL、Oracle可替换为xacc.AccCode || '%'。
这个版本的计算结果和示例规则完全对齐:
- AccCode为1的账户:匹配所有以1为前缀的Sub账户(111、112、113、121、122),累计金额2400
- AccCode为11的账户:匹配所有以11为前缀的Sub账户(111、112、113),累计金额1600
- AccCode为12的账户:匹配所有以12为前缀的Sub账户(121、122),累计金额800
通用递归版本(不受编码规则限制)
如果后续AccCode的编码逻辑调整(比如不再固定1位为一层),可以用递归CTE实现通用的树状汇总,完全不依赖前缀匹配规则:
WITH RECURSIVE acc_tree AS ( -- 锚点层:提取所有Sub账户的原始发生额 SELECT AccID, AccCode, TheAmount FROM tblaccounts JOIN tblamounts ON tblaccounts.AccID = tblamounts.AccID WHERE AccType = 'Sub' UNION ALL -- 递归层:将下属金额向上汇总到所有上级Main账户 SELECT parent.AccID, parent.AccCode, child.TheAmount FROM acc_tree child JOIN tblaccounts parent ON child.AccCode LIKE CONCAT(parent.AccCode, '%') AND parent.AccCode != child.AccCode ) SELECT AccCode, SUM(TheAmount) AS TotalAmount FROM acc_tree GROUP BY AccCode
内容的提问来源于stack exchange,提问作者Hamada
相关产品推荐
相关产品推荐

