首页 > 代码库 > sql rollup解决责任人收支余额

sql rollup解决责任人收支余额

 问题的提出是周聪之前问过我的项目往来查询,不好在NC上一次性查询到。然后我就搞了一个很长的项目对账,发布了NC的节点。

现在我做了总二的总账,每次领导问我项目还有多少钱,收了多少付了多少,我还要通过科目余额表查询,但是悲剧的是工程施工必须剔除月末配比的暂估,给13打电话,

她也说NC没有法子查询一级科目然后还去掉某一个末级。

 查询结果出来之后,我想对一级科目做一个汇总,实现下图的效果

sql外面嵌套了一层,因为发现虽然第一层有了wm_concat,可是使用rollup根本无用,后来发现使用rollup看来必须要对数字列进行聚合sum

 select valuecode, valuename,explanation,km,dispname,sum(借方),sum(贷方),yearv,       periodv ,       no    from ( select   wmsys.wm_concat(gl_freevalue.valuecode) valuecode,         wmsys.wm_concat(gl_freevalue.valuename) valuename,       gl_detail.explanation,       substr(bd_accsubj.dispname,1,4) km,       bd_accsubj.dispname,         gl_detail.debitamount 借方,       gl_detail.creditamount 贷方,       gl_detail.yearv,       gl_detail.periodv ,       gl_voucher.no         from bd_accsubj  join gl_detail    on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj  join bd_glorgbook    on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook  join gl_voucher    on gl_detail.pk_voucher = gl_voucher.pk_voucher  left join gl_freevalue    on gl_detail.assid = gl_freevalue.freevalueid where gl_detail.dr = 0   and  gl_detail.yearv>=2014    and gl_detail.pk_systemv!=TR--去掉年末的结转凭证    and gl_detail.periodv<>00 --去掉期初and bd_accsubj.subjcode not in(41040107,41040109,41040207,41040209,41040307,41040308,560106)--去掉月末配比和交税的所得税   and bd_glorgbook.glorgbookcode = 015201-0001--公司条件   and gl_freevalue.valuecode like 015211% --项目责任人条件group by   gl_detail.explanation,           bd_accsubj.dispname,             gl_detail.yearv,           gl_detail.periodv,           gl_voucher.no,           gl_detail.creditamount,           gl_detail.debitamount)   group by valuecode, valuename ,km, rollup((explanation,dispname,yearv,periodv,no))        order by valuecode,dispname, yearv, periodv ,no

 

昨天晚上再次想到一个问题,完全可以不用考虑wm_concat了,因为where条件的valuecode直接限制了工程项目而没有了“部门档案”

这样一来,sql可以这样

select   gl_freevalue.valuecode,          gl_freevalue.valuename,        gl_detail.explanation,       substr(bd_accsubj.dispname,1,4) km,       nvl(bd_accsubj.dispname,小计) 科目,        -- gl_detail.debitamount,       --gl_detail.creditamount,      sum(gl_detail.debitamount) 支出,     sum(gl_detail.creditamount) 收入,       gl_detail.yearv||-||gl_detail.periodv 期间,        gl_voucher.no      from bd_accsubj  join gl_detail    on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj  join bd_glorgbook    on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook  join gl_voucher    on gl_detail.pk_voucher = gl_voucher.pk_voucher  left join gl_freevalue    on gl_detail.assid = gl_freevalue.freevalueid where gl_detail.dr = 0   and  gl_detail.yearv=2014    and gl_detail.pk_systemv!=TR--去掉年末的结转凭证    and gl_detail.periodv<>00 --去掉期初and bd_accsubj.subjcode not in(41040107,41040109,41040207,41040209,41040307,41040308,560106)--去掉月末配比和交税的所得税   and bd_glorgbook.glorgbookcode = 015201-0001--公司条件   and gl_freevalue.valuecode like 015211% --项目责任人条件 group by substr(bd_accsubj.dispname,1,4),        rollup((gl_freevalue.valuecode,              gl_freevalue.valuename,               gl_detail.explanation,              bd_accsubj.dispname,                gl_detail.yearv,             gl_detail.periodv,             gl_voucher.no ))        order by substr(bd_accsubj.dispname,1,4),gl_freevalue.valuecode,yearv, periodv ,no

 

通过这个sql对rollup有了进一步的理解,group by rollup中前面的group by要的是根据什么组求和的,就像这里的取前四位的一级会计科目2123 4104,然后后面的就是正常的使用group 必须要写的东西。

之前的小计都是在最后面,最终发现还是order by的问题,排序第一个需要是group by后面的东西才能让小计分组在后面而不是都跑在最后面

分组小计的rollup继续的例子