求助科目余额CTE求和

qq_28607107 2017-12-15 11:08:44

下面是测试表，tablea 为凭证数据，tableb为ID对应的上级ID，table为科目余额表
要求将，tablea的ID去tableb找到上级id,将结果求和到tablec ，要加上tablec数据并且如果tablec没有上级ID和本ID时，插入行。
不一定要按我说的这么做，可以先删除再插入。我意思是这样，新手好复杂不会写求大神指点

为节约大神时间，下图是测试数据

Create table tablea

 

(  

 

id varchar(100) ,  

debit decimal(20,2),

credit decimal(20,2)

)  

  

 Create table tableb

 

(  

 

id varchar(100) ,  

code varchar(100) , 

pid varchar(100),

pidcode varchar(100)

)  



 Create table tablec

 

(  

 

id varchar(100) ,  

debit decimal(20,2),

credit decimal(20,2)

) 

  

insert into tablea(id,debit,credit)   

select 'DC000058',10,0

union all  

select 'DC000063',0,10

union all  

select 'DC000011',50,60

union all  

select 'DC000025',60,50

union all  

select 'DC000044',50,50

union all  

select 'DC000011',50,50





insert into tableb(id,code,pid,pidcode)   

select 'DC000058','10010102','DC000001','100101'

union all  

select 'DC000001','100101','DC000002','1001'

union all  

select 'DC000063','1002',null,null

union all  

select 'DC000025','100301','DC000045','1003'

union all  

select 'DC000044','100401','DC000002','1001'

union all  

select 'DC000011','1006','DC000001','100101'





insert into tablec(id,debit,credit)  

select 'DC000001',10,20

union all  

select 'DC000011',0,0

union all  

select 'DC000058',20,30

union all  

select 'DC000063',0,0

union all  

select 'DC000011',0,0

union all  

select 'DC000044',10,10