总部分公司往来查询所有分公司[工程部统计总部扣所有分公司信息化]

之前的强大good sql只可以通过一个个输入分公司代码查询单一分公司和总部的往来，昨天江斌问我要所有扣分公司项目信息化20万的表，于是这个需求来了

昨天想了好久，通过先查询一个凭证中是总部的pk_detail，然后再查询所有是往来的（通过rn>=2）减去总部的，或者子查询pk_detail总部的就可以了。

sql会很重复，很长，但是能解决。

昨天写了，可是查询结果是空。

无望了。

刚刚来马晓莉家，想通了，pk_detail是一行一行的，应该查询pk_voucher，然后再detail

下面是总部往来的pk_detail

select *
  from (select count(distinct gl_freevalue.valuecode) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               gl_voucher.pk_voucher,
               gl_freevalue.valuecode,
               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.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 in ('010101', '&a')
           and bd_bdinfo.bdname='客商辅助核算'
           )
 where rn >= 2
   and valuecode = '010101'
   /*and ex not like '上收%'  
   and ex not like '下拨%'  */
   and substr(ex,1,2)<>'上收'
   and substr(ex,1,2)<>'下拨' 
   and  ex not like '%0.4%'   
 order by vdate, no

 

sql

select 
               --gl_voucher.pk_voucher,
               --gl_detail.pk_detail,
               gl_freevalue.valuecode,
               gl_freevalue.valuename,
               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.valuename,
               --gl_detail.pk_systemv,
               gl_detail.detailindex
          from bd_accsubj
          join gl_detail
            on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj       
          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 
 bd_bdinfo.bdname='客商辅助核算'--删除制单中的银行账号和7777往来
 and  gl_freevalue.valuecode <> '010101'--删除总部的
 and (gl_detail.debitamount=200000 or gl_detail.creditamount=200000 )--信息化扣款20万，如果是汇总扣或退则不行
 and  gl_voucher.pk_voucher in
 (
 select pk_voucher from 
 (
 select *
  from (select count(distinct gl_freevalue.valuecode) over(partition by gl_detail.prepareddatev, gl_voucher.no) rn,
               gl_voucher.pk_voucher,
               gl_detail.pk_detail,
               gl_freevalue.valuecode,
               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.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 in ('010101', '&a')
           and bd_bdinfo.bdname='客商辅助核算' 
           )
 where rn >= 2
   and valuecode = '010101'
   /*and ex not like '上收%'  
   and ex not like '下拨%'  */
   and substr(ex,1,2)<>'上收'
   and substr(ex,1,2)<>'下拨' 
   and  ex not like '%0.4%'   
 )
 
 )  
 order by valuecode,vdate, no