北京仅项目发生客商sql_partition、minus

提出要求：北京公司在上面join用法写出项目的客商支付sql，继而要求一些凭证仅有项目没有客商，需要把这些剔除掉，sql写法用到了partition和minus用法，partition里面改进了依照gl_voucher.prepareddate分组，因为年份之内gl_voucher.prepareddate不一样，因此分组之后可以查询年份内的，否则查询年份内的数据会有问题。

(select distinct
       gl_detail.prepareddatev 制单日期,      
       gl_voucher.no 凭证号,
       gl_voucher.totaldebit 借方合计,
       gl_voucher.explanation 摘要
       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 gl_detail.periodv='07'*/
   and bd_glorgbook.glorgbookcode = '010201-0001'
   and gl_detail.explanation<>'期初'
   /*and bd_bdinfo.bdname='工程项目'*/
   and bd_accsubj.subjcode like '4104%'
   and bd_accsubj.subjcode not in('41040107','41040109','41040207','41040209','41040307','41040308') )
   
 minus
 
 (select distinct 制单日期,凭证号,借方合计,摘要 from (
select  gl_detail.explanation,
        bd_accsubj.dispname,
        gl_detail.debitamount 借方,
        gl_detail.creditamount 贷方,
        gl_detail.prepareddatev 制单日期,        
        gl_voucher.no 凭证号,
        gl_voucher.totaldebit 借方合计,
        gl_voucher.explanation 摘要,
        gl_freevalue.valuecode ,
        gl_freevalue.valuename ,
       gl_detail.detailindex,
       gl_detail.pk_systemv ,
       sum(decode(length(gl_freevalue.valuecode),11,1,0)) over(partition by gl_voucher.prepareddate,gl_voucher.no) x1,
       sum(decode(length(gl_freevalue.valuecode),10,1,0)) over(partition by gl_voucher.prepareddate,gl_voucher.no) x2
  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.explanation<>'期初'
   and gl_detail.yearv >= '2010'
/*   and gl_detail.periodv='07'*/
   and bd_glorgbook.glorgbookcode = '010201-0001'
   and (length(gl_freevalue.valuecode)='10' or length(gl_freevalue.valuecode)='11')
   )
   where x1>0 and x2>0 )
   order by 制单日期,凭证号

效果图：