结算中心公司往来sql

2013-02-24 23:00:16 更新：特别增加 and gl_freevalue.valuecode<>'777777' ，显示结果易读性增强

说明：此sql解决结算中心分子公司之间往来凭证查询，比如总部和山东往来，标准NC没有此功能。

本想另外的写法“count(*)over(partition by cola,colb) rn  .. where rn>=2”（辰影提供），但是想到，如果一个凭证出现了多行总部010101，就不好处理

23:44:10 更新，增加每个公司的金额 （通过修改wmsys.wm_concat(valuecode) vc）,

select *
  from (select 制单日期,
               凭证号,
               wmsys.wm_concat(explanation) ex,
               wmsys.wm_concat(valuecode||'(' ||'D:'||借方||','||'C:'||贷方||')') vc,  
               count(valuecode) 个数,            
               wmsys.wm_concat(辅助名) vn               
          from (select gl_detail.explanation,
                       bd_accsubj.dispname,
                       gl_detail.debitamount 借方,
                       gl_detail.creditamount 贷方,
                       gl_detail.prepareddatev 制单日期,
                       gl_voucher.no 凭证号,
                        bd_bdinfo.bdname  辅助名,
                       gl_freevalue.valuecode  valuecode,
                       gl_freevalue.valuename  valuename,
                       gl_detail.pk_systemv,
                       gl_detail.detailindex
                  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
                  left join bd_bdinfo
                    on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
                 where gl_detail.dr = '0'
                   and gl_detail.yearv >= '2010'
                   and bd_glorgbook.glorgbookcode = '0100-0001'
                   and gl_detail.explanation <> '期初'
                   and gl_freevalue.valuecode<>'777777'
                 --and bd_accsubj.subjcode like '150103%'
                 )
         group by 制单日期, 凭证号)
 where instr(vc, '010101') > 0
   and instr(vc, '015101') > 0
   and instr(ex, '上收') = 0
   and instr(ex, '下拨') = 0
order by 制单日期,凭证号

2013-02-25 08:49:56 更新：

昨天晚上想到了通过两表查询，通过制单日期和凭证号做条件，可以只查询其中一个分公司的。.

09:12:31 更新：

通过在a.*前面增加distinct，去掉重复记录

09:25:13 更新：

去掉上收、下拨资金

这里如果使用and instr(ex, '上收') = 0 and instr(ex, '下拨') = 0 耗时很大，因为它需要从所有字节检查上收下拨字段

             select distinct a.*from
                (select gl_detail.explanation ex,
                       bd_accsubj.dispname,
                       gl_detail.debitamount debit,
                       gl_detail.creditamount credit,
                       gl_detail.prepareddatev vdate,
                       gl_voucher.no ,
                        bd_bdinfo.bdname ,
                       gl_freevalue.valuecode ,
                       gl_freevalue.valuename ,
                       gl_detail.pk_systemv,
                       gl_detail.detailindex
                  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
                  left join bd_bdinfo
                    on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
                 where gl_detail.dr = '0'
                   and gl_detail.yearv >= '2012'
                   and bd_glorgbook.glorgbookcode = '0100-0001'
                   and gl_detail.explanation <> '期初'
                 --and bd_accsubj.subjcode like '150103%'
                   and gl_freevalue.valuecode<>'777777'
                   and  gl_freevalue.valuecode='015101')a,                    
           (select gl_detail.explanation ex,
                       bd_accsubj.dispname,
                       gl_detail.debitamount debit,
                       gl_detail.creditamount credit,
                       gl_detail.prepareddatev vdate,
                       gl_voucher.no ,
                        bd_bdinfo.bdname ,
                       gl_freevalue.valuecode ,
                       gl_freevalue.valuename ,
                       gl_detail.pk_systemv,
                       gl_detail.detailindex
                  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
                  left join bd_bdinfo
                    on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
                 where gl_detail.dr = '0'
                   and gl_detail.yearv >= '2012'
                   and bd_glorgbook.glorgbookcode = '0100-0001'
                   and gl_detail.explanation <> '期初'
                 --and bd_accsubj.subjcode like '150103%'
                   and gl_freevalue.valuecode<>'777777'
                   and  gl_freevalue.valuecode='010101')b
           where a.vdate||a.no=b.vdate||b.no
                 and  a.ex not like '上收%'  
                 and  a.ex not like '下拨%'                                 
                order by a.vdate,a.no

 11:39:13 极大简化版，partition

13:42:26 通过变量赋值

 and gl_freevalue.valuecode in ('010101',  '&a' ))
 where rn >= 2
   and valuecode =  '&a'
 order by vdate, no

select *
  from (select count(distinct gl_freevalue.valuecode) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               gl_detail.explanation ex,
               bd_accsubj.dispname,
               gl_detail.debitamount debit,
               gl_detail.creditamount credit,
               gl_detail.prepareddatev vdate,
               gl_voucher.no,
               bd_bdinfo.bdname,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               gl_detail.pk_systemv,
               gl_detail.detailindex
          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
          left join bd_bdinfo
            on gl_freevalue.checktype = bd_bdinfo.pk_bdinfo
         where gl_detail.dr = '0'
           and gl_detail.yearv >= '2013'
           and bd_glorgbook.glorgbookcode = '0100-0001'
           and gl_detail.explanation <> '期初'
              --and bd_accsubj.subjcode like '150103%'
           and gl_freevalue.valuecode <> '777777'
           and gl_freevalue.valuecode in ('015101', '010101'))
 where rn >= 2
   and valuecode = '010101'
 order by vdate, no