通过递归函数实现多父级求和

问题来自论坛：这样的SQL如何实现
解题来自坛主：唐诗300首

在数据库设计中，经常会设计有多层父子关系的一张表；

此时，可以通过递归函数查询出子级上的所有层级的父级（或父级下所有的子级）；

然后再根据所有子级求和操作，得到父级下所有的子级求和。

举例说明（例子来自于论坛这样的需求SQL如何实现）

第一步：准备测试数据

CREATE TABLE TAccount(AccountId varchar(50),AccountName varchar(50),ParentAccountId varchar(50))
INSERT INTO TAccount
SELECT '1002','银行存款','' UNION ALL
SELECT '100201','A银行','1002' UNION ALL
SELECT '100202','B银行','1002' UNION ALL
SELECT '100203','C银行','1002' UNION ALL
SELECT '10020301','C银行','100203' 

--表B科目余额表
CREATE TABLE AccountBalance(AccountId varchar(50),Year int,Month int,Amount numeric(19,9))
INSERT INTO AccountBalance
SELECT '100201',2020,1,100 UNION ALL
SELECT '100202',2020,1,300 UNION ALL
SELECT '10020301',2020,1,0 UNION ALL
SELECT '100201',2020,2,150 UNION ALL
SELECT '100202',2020,2,800 UNION ALL
SELECT '10020301',2020,2,500 

第二步：通过递归函数获取层级关系；（查询出子级对应的所有层级的父级）

create FUNCTION [dbo].[getchild]
(	
	@paccount varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
with t1 as (
select AccountId from TAccount as a where AccountId=@paccount
union all 
select a.AccountId from TAccount as a inner join t1 as b on a.ParentAccountId=b.AccountId
)select * from t1
)

第三步：连接查询，父级ID传入函数（查询父级下所有子级的和）

select 科目代码=a.AccountId,
       年=b.Year,
       月=b.Month,
       期末余额=cast(t.Amount as int)
 from TAccount a
 cross join (select distinct Year,Month from AccountBalance) b
 outer apply(select Amount=sum(x.Amount)
             from AccountBalance x 
             where x.Year=b.Year
             and x.Month=b.Month
             and x.AccountId in(select * from getchild(a.AccountId))) t
			 order by 年,月,科目代码

/*科目代码	年	月	期末余额
1002	2020	1	400
100201	2020	1	100
100202	2020	1	300
100203	2020	1	0
10020301	2020	1	0
1002	2020	2	1450
100201	2020	2	150
100202	2020	2	800
100203	2020	2	500
10020301	2020	2	500
*/

思路二：CTE中直接递归查询出子级所有层级的父级，且每个父+子一条记录。实现代码如下：

;with tr as
(select pid=x.AccountId,sid=y.AccountId
  from (select distinct AccountId from TAccount
        union 
        select distinct ParentAccountId from TAccount) x
  left join TAccount y on x.AccountId=y.ParentAccountId
  where x.AccountId<>''
 union all
 select pid=a.pid,sid=b.AccountId
  from tr a
  inner join TAccount b on a.sid=b.ParentAccountId)
select 科目代码=a.AccountId,
       年=b.Year,
       月=b.Month,
       期末余额=cast(isnull(t.Amount,0) as int)
 from TAccount a
 cross join (select distinct Year,Month from AccountBalance) b
 outer apply(select Amount=sum(x.Amount)
             from AccountBalance x 
             where x.Year=b.Year
             and x.Month=b.Month
             and x.AccountId in(select sid=a.AccountId
                                union 
                                select sid from tr where pid=a.AccountId and sid is not null)) t

/*
科目代码                 年           月           期末余额
-------------------- ----------- ----------- -----------
1002                 2020        1           400
100201               2020        1           100
100202               2020        1           300
100203               2020        1           0
10020301             2020        1           0
1002                 2020        2           1450
100201               2020        2           150
100202               2020        2           800
100203               2020        2           500
10020301             2020        2           500

(10 行受影响)
*/

问题来自论坛：这样的SQL如何实现
解题来自坛主：唐诗300首