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  */