sql 凭证明细表 科目余额表_转用友科目余额表sql和软件中的一样

select ‘A1’ Note , g.ccode,sum(case cbegind_c when ‘借’ then mb else 0 end ) J,sum(case cbegind_c when ‘贷’ then mb else 0 end ) D into #tbl from ufdata_001_2013..gl_accsum g join ufdata_001_2013..code c on g.ccode=c.ccode where iperiod=1 and c.bend=1 and (c.bsup=0 and c.bcus =0 and c.bperson=0 and c.bdept=0) group by g.ccode

insert #tbl select ‘A2’,g.ccode, sum(case cbegind_c  when ‘借’ then mb else 0 end ) J,sum(case cbegind_c  when ‘贷’ then mb else 0 end ) D from ufdata_001_2013..gl_accass g  join ufdata_001_2013..code c on g.ccode=c.ccode where iperiod=1 and c.bend=1 group by g.ccode

insert #tbl select ‘B’,g.ccode ,sum(md) ,sum(mc) from ufdata_001_2013..code c join ufdata_001_2013..gl_accvouch g on g.ccode=c.ccode where iflag is null and iperiod between 1 and 2 group by g.ccode

insert #tbl select ‘C’,g.ccode ,sum(md) fsJ, sum(mc) fsD from ufdata_001_2013..code c  join ufdata_001_2013..gl_accvouch g on g.ccode=c.ccode where iflag is null and  iperiod =3 group by g.ccode

insert #tbl select note ,left(ccode ,8) ccode , sum(J)  j ,sum(D ) d from #tbl  where len(ccode)=10 group by note ,left(ccode ,8)

insert #tbl select note ,left(ccode ,6) ccode , sum(J)  j ,sum(D ) d from #tbl  where len(ccode)=8 group by note ,left(ccode ,6)

insert #tbl select note ,left(ccode ,4) ccode , sum(J)  j ,sum(D ) d from #tbl  where len(ccode)=6 group by note ,left(ccode ,4)

select COALESCE( t1.ccode,t2.ccode,t3.ccode) ccode ,qcJ,qcD,fsJ,FsD,qmJ,qmD  into #tbl3 from (select ccode , (case when sum(j)-sum(d)  >0 then  sum(j)-sum(d) else  0 end) as  qcJ,(case when sum(j)-sum(d)  ‘c’ group by ccode) as t1

full join (select ccode ,  sum(j) fsJ,sum(d)  fsD from #tbl where  note=’c’ group by ccode ) as t2 on t1.ccode=t2.ccode

full join (select ccode , (case when sum(j)-sum(d)  >0 then  sum(j)-sum(d) else  0 end) as  qmJ,(case when sum(j)-sum(d) 

select  * from #tbl3 union all  select  ‘9合计’ ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from  #tbl3  where ccode like ‘____’ union all  select  ‘1资产小计’ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from  #tbl3  where ccode like ‘1___’ union all  select  ‘2负债小计’ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from  #tbl3  where ccode like ‘2___’ union all  select  ‘3权益小计’ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from  #tbl3  where ccode like ‘3___’ union all  select  ‘5损益小计’ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from  #tbl3  where ccode like ‘5___’ order by 1

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树SQL高级技巧透视表 31839 人正在系统学习中 相关资源：凯歌软件安装管理器1.0-其它文档类资源-CSDN文库

来源：夏骁凯