MYSQL解决科目余额表汇总问题

近期在研究自己开发财务软件，使用的spring+mysql的技术，其中有一项需求是将科目余额表中的科目金额汇总求和到上级科目，因科目级数不确定，所以不能简单地group by+jion的方式，经查询网上资料，现提出两种解决方案：

1、-- 逐级汇总查询

因科目编号较为规则，末级科目包含了上级科目的编号，所以用like方式向上汇总即可

SELECT 
a.account_num,
a.account,
IfNULL(SUM(b.income),0) income,
IfNULL(SUM(b.pay),0) pay
FROM account a  
LEFT JOIN account b 
on b.account_num LIKE CONCAT(a.account_num, '%')
GROUP BY a.account_num,a.account
ORDER BY a.account_num

查询结果

2、with as方式
大部分情况下树形结构的父子节点编号不会是这样规则的，所以需要递归遍历。

mysql从8.0开始支持with as语句，可以满足需求，此语句具体使用方式请自行百度

with recursive t1(account_num,up_num,income,pay) as (
select t0.account_num,account_num,IFNULL(t0.income,0),IFNULL(t0.pay,0) 
from account t0 
-- WHERE account_num in(
-- with recursive s1(account_num) as (
-- select t0.account_num from account t0 where account_num="1002"
-- union all
-- select t2.account_num
--   from account t2, s1  
--  where t2.parent_num = s1.account_num
-- )
-- SELECT account_num FROM s1
-- )
 union all
select t2.account_num,t1.up_num st,IFNULL(t2.income,0),IFNULL(t2.pay,0)
from account t2, t1  
where t2.parent_num = t1.account_num)
select 
up_num account_num,
a.account,
sum(t1.income) income,
sum(t1.pay) pay
  from  t1,account a
WHERE a.account_num=t1.up_num
GROUP BY up_num,a.account
order by t1.up_num;

查询结果
这一方式还有一个优点，就是可以走索引，查询速度要比like方式快很多，而且with as内部可以嵌套with as，实现层级查询的效果

个人仅提供一思路，大家在使用过程中请自行加以验证
还有一种方式是创建函数(5.8以下版本)，但函数的些复杂，可移植性较差