xzgguo 发表于 2019-4-14 21:16:21

跪谢!!!查询用友科目余额表的sql代码,哪位大神共享一下?

跪谢!!!查询用友科目余额表的sql代码,哪位大神共享一下?

HLJ_C 发表于 2019-4-14 21:30:04

同求，跪谢

HLJ_C 发表于 2019-4-14 21:48:45

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_666_2017..gl_accsum g join ufdata_666_2017..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_cwhen '借' then mb else 0 end ) J,sum(case cbegind_cwhen '贷' then mb else 0 end ) D from UFDATA_666_2017..gl_accass gjoin UFDATA_666_2017..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_666_2017..code c join UFDATA_666_2017..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_666_2017..code cjoin UFDATA_666_2017..gl_accvouch g on g.ccode=c.ccode where iflag is null andiperiod =3 group by g.ccode
insert #tbl select note ,left(ccode ,8) ccode , sum(J)j ,sum(D ) d from #tblwhere len(ccode)=10 group by note ,left(ccode ,8)
insert #tbl select note ,left(ccode ,6) ccode , sum(J)j ,sum(D ) d from #tblwhere len(ccode)=8 group by note ,left(ccode ,6)
insert #tbl select note ,left(ccode ,4) ccode , sum(J)j ,sum(D ) d from #tblwhere len(ccode)=6 group by note ,left(ccode ,4)
select COALESCE( t1.ccode,t2.ccode,t3.ccode) ccode ,qcJ,qcD,fsJ,FsD,qmJ,qmDinto #tbl3 from (select ccode , (case when sum(j)-sum(d)>0 thensum(j)-sum(d) else0 end) asqcJ,(case when sum(j)-sum(d)<0 thensum(d)-sum(j) else0 end)asqcD from #tbl wherenote<>'c' group by ccode) as t1
full join (select ccode ,sum(j) fsJ,sum(d)fsD from #tbl wherenote='c' group by ccode ) as t2 on t1.ccode=t2.ccode
full join (select ccode , (case when sum(j)-sum(d)>0 thensum(j)-sum(d) else0 end) asqmJ,(case when sum(j)-sum(d)<0 thensum(d)-sum(j) else0 end)asqmDfrom #tblgroup by ccode ) as t3 on t1.ccode=t3.ccode
select* from #tbl3 union allselect'9合计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from#tbl3where ccode like '____' union allselect'1资产小计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from#tbl3where ccode like '1___' union allselect'2负债小计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from#tbl3where ccode like '2___' union allselect'3权益小计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from#tbl3where ccode like '3___' union allselect'5损益小计' ccode, sum(qcj),sum(qcd),sum(fsj),sum(fsd),sum(qmj),sum(qmd) from#tbl3where ccode like '5___' order by 1
群里大神写的，我搬运过来了注意修改数据的名称   建立的临时表用后 记得删掉dropTABLE #tbl

sem_leo@126.com 发表于 2019-4-14 22:18:27

大神，膜拜你们

xzgguo 发表于 2019-4-14 22:40:28

HLJ_C 发表于 2019-4-14 21:48
select 'A1' Note , g.ccode,sum(case cbegind_c when '借' then mb else 0 end ) J,sum(case cbegind_c wh ...

为什么我的运行不起,提示这个如图,
另我的是用友通t3 11.0版本的

HLJ_C 发表于 2019-4-15 21:20:13

我运行的是U8

xzgguo 发表于 2019-4-16 11:50:32

HLJ_C 发表于 2019-4-15 21:20
我运行的是U8

那用友通t3，代码要怎么改一下，大神？

HLJ_C 发表于 2019-4-16 17:20:49

xzgguo 发表于 2019-4-16 11:50
那用友通t3，代码要怎么改一下，大神？

我感觉你什么都不会呢    但凡会一点 都能看懂这些语句   你不会你要什么语句呢？

xzgguo 发表于 2019-4-17 18:11:33

HLJ_C 发表于 2019-4-16 17:20
我感觉你什么都不会呢    但凡会一点 都能看懂这些语句   你不会你要什么语句呢？

大神，我是菜鸟，帮忙看看怎么改呢，跪谢

lzgzmn 发表于 2019-5-6 22:56:35

跟踪一下不就有了

ming520186 发表于 2019-5-8 14:55:14

跟踪数据库就能得到逻辑。

又一个四爷 发表于 2019-5-10 20:16:38

学习了，不错

xzgguo 发表于 2019-5-16 15:14:05

跟踪数据库不会怎么办？

xinhuanqing 发表于 2021-3-5 21:40:44

HLJ_C 发表于 2019-4-14 21:48
select 'A1' Note , g.ccode,sum(case cbegind_c when '借' then mb else 0 end ) J,sum(case cbegind_c wh ...

这个代码可以查询出来数据。但是，第一没有期间选择，第二数据有错误。

夏雪冬阳78 发表于 2022-6-5 17:42:54

提示的意思是连接不上数据库吧
页: [1]
查看完整版本: 跪谢!!!查询用友科目余额表的sql代码,哪位大神共享一下?