这样的需求SQL如何实现

小野马1209 2020-06-29 11:32:15 

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

...全文
256 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
XXXXn帅气 2020-06-30
  • 打赏
  • 举报
 回复
引用 8 楼 唐诗三百首 的回复:
版主思路就是清晰 学到了学到老
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
  • 打赏
  • 举报
 回复
引用 1 楼 唐诗三百首 的回复:


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 行受影响)

*/
这样会有个问题，我昨天的案例不是很全，比如我增加个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
  • 打赏
  • 举报
 回复 1
引用 2 楼 向阳的花儿 的回复:
另一种思路，根据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,科目代码

这样会有个问题，我昨天的案例不太好，比如我增加个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
  • 打赏
  • 举报
 回复 1
另一种思路，根据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,科目代码
msbase.jar，mssqlserver.jar，msutil.jar
JDBC Driver for SQL Server:msbase.jar，mssqlserver.jar，msutil.jar
PLSQL_Developer8.0绿色版64位
64位的PLSQL_Developer8.0绿色版 不需要安装，解压后可以直接使用
mybatis-3.2.3 jar包和源码
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis
Oracle11g客户端精简版
Oracle11g客户端精简版,WIN7上测试通过,绝对能用,安装完成后,请参阅readme.txt文件修改即可.
mysql-connector-java-5.1.30工具
MySQL官方提供的驱动包 mysql-connector-java-5.1.30.zip
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章