之前的强大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