27,579
社区成员
发帖
与我相关
我的任务
分享这样的需求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'
--表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 '100201',2020,2,150 UNION ALL
SELECT '100202',2020,2,800
SELECT * FROM TAccount
SELECT * FROM AccountBalance
...全文
请发表友善的回复…
发表回复
XXXXn帅气 2020-06-30
- 打赏
- 举报
版主思路就是清晰 学到了学到老
XXXXn帅气 2020-06-30
- 打赏
- 举报
;with c as(
select ROW_NUMBER()over (order by a.AccountId)as ID,0as NUM,* from TAccount as a where ParentAccountId=''
union all select a.ID as ID,a.NUM+1as NUM,b.* from c as a join TAccount as b on a.AccountId=b.ParentAccountId
)
select c.AccountId,b.Year,b.Month,t.Amount from c cross join (select distinct [Year],[Month] from AccountBalance) b
outer apply(select Amount=sum(x.Amount)
from AccountBalance x join c as x1 on x.AccountId=x1.AccountId
where x.Year=b.Year
and x.Month=b.Month
and c.ID=x1.ID and x1.NUM>=c.NUM and CHARINDEX(c.AccountId,x1.AccountId)>0) t
order by b.Year,b.Month,c.AccountId
/*
1002 2020 1 400.000000000
100201 2020 1 100.000000000
100202 2020 1 300.000000000
100203 2020 1 0.000000000 1
10020301 2020 1 0.000000000 1
1002 2020 2 1450.000000000
100201 2020 2 150.000000000
100202 2020 2 800.000000000
100203 2020 2 500.000000000
10020301 2020 2 500.000000000
*/
唐诗三百首 2020-06-30
- 打赏
- 举报
;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 行受影响)
*/
向阳的花儿 2020-06-30
- 打赏
- 举报
那你这就是不同的需求了；
用我的那个方法实现起来就困难了；
需要写一个递归函数来获取所有子级来实现；
递归函数：
--获取本级及所有下级
create FUNCTION getchild
(
@account varchar(20)
)
RETURNS TABLE
AS
RETURN
(
with t1 as (
select AccountId from TAccount as a where AccountId=@account
union all
select a.AccountId from TAccount as a inner join t1 as b on a.ParentAccountId=b.AccountId
)select * from t1
)
GO
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
*/
xxfvba 2020-06-30
- 打赏
- 举报
;with cte as
(select AccountId,ParentAccountId from TAccount where ParentAccountId<>''
union all
select #TAccount.AccountId,cte.ParentAccountId from cte, TAccount where TAccount.ParentAccountId=cte.AccountId)
select a.ParentAccountId,Year,Month,SUM(Amount) Amount from cte a,AccountBalance b where a.AccountId=b.AccountId group by a.ParentAccountId,Year,Month
union all
select * from AccountBalance
order by Year,Month, a.ParentAccountId
小野马1209 2020-06-30
- 打赏
- 举报
这样会有个问题，我昨天的案例不是很全，比如我增加个100203和10020301的科目，因为余额表只有明细科目，导致统计1002的时候只统计到了100201和100202的科目 少汇总了100203
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 小野马1209 2020-06-30
- 打赏
- 举报
这样会有个问题，我昨天的案例不太好，比如我增加个100203和10020301的科目，因为余额表只有明细科目，导致统计1002的时候只统计到了100201和100202的科目 少汇总了100203
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 
唐诗三百首 2020-06-29
- 打赏
- 举报
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 AccountId
from TAccount y
where y.AccountId=a.AccountId
or y.ParentAccountId=a.AccountId)) t
/*
科目代码 年 月 期末余额
-------------------- ----------- ----------- -----------
1002 2020 1 400
100201 2020 1 100
100202 2020 1 300
1002 2020 2 950
100201 2020 2 150
100202 2020 2 800
(6 行受影响)
*/
向阳的花儿 2020-06-29
- 打赏
- 举报
另一种思路，根据parentid group by，再uion原始表就可以了。
select b.ParentAccountId as 科目代码,Year 年,Month 月,sum(Amount) as 期末余额 from AccountBalance as a
left join TAccount as b on a.AccountId=b.AccountId
group by b.ParentAccountId,Year,Month
union all
select * from AccountBalance
order by Year,Month,科目代码