sql 财务科目余额表写法

-->生成测试数据 

 declare @科目表 table([科目ID] int,[科目编码] nvarchar(10),[科目名称] nvarchar(5)) 
 Insert @科目表 
 select 1,N'1001',N'现金' union all 
 select 2,N'1002',N'银行存款' union all 
 select 3,N'1002-01',N'工商银行' union all 
 select 4,N'1002-02',N'农业银行' union all 
 select 100,N'2171',N'应交税金' union all 
 select 101,N'2171-01',N'应交增值税' union all 
 select 102,N'2171-01-01',N'进项税额' union all 
 select 103,N'2171-01-02',N'已交税金' union all 
 select 104,N'2171-02',N'未交增值税' union all 
 select 105,N'2171-03',N'应交营业税' 
 --Select * from @科目表 

 declare @业务发生表 table([科目ID] int,[年度] Datetime,[月度] int,[借方金额] int,[贷方金额] int) 
 Insert @业务发生表 
 select 1,'2008',5,90,10 union all 
 select 4,'2008',5,20,30 union all 
 select 102,'2008',5,1,4 union all 
 select 105,'2008',5,100,200 
 --Select * from @业务发生表 

 select  
     b.科目ID,b.科目编码,b.科目名称,sum(c.借方金额) as [借方金额],sum(c.贷方金额) as [贷方金额]  
 from  
      @科目表 a  
 join  
      @科目表 b on a.科目编码 like b.科目编码+'%' 
 join 
     @业务发生表 c on c.科目ID=a.科目ID 
 where 
     a.科目ID in (102,105) 
 group by b.科目ID,b.科目编码,b.科目名称 
 /* 
 科目ID        科目编码       科目名称  借方金额        贷方金额 
 ----------- ---------- ----- ----------- ----------- 
 100         2171       应交税金  101         204 
 101         2171-01    应交增值税 1           4 
 102         2171-01-01 进项税额  1           4 
 105         2171-03    应交营业税 100         200 
 */ 
 /* 
 科目ID        科目编码       科目名称  借方金额        贷方金额 
 ----------- ---------- ----- ----------- ----------- 
 100         2171       应交税金  100         200 
 105         2171-03    应交营业税 100         200 
 */