北京仅项目发生sql_partition、minus

 (select distinct
       gl_detail.prepareddatev 制单日期,
       gl_voucher.no 凭证号,
       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 = '2011'
   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%' )
 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.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.no) x1,
       sum(decode(length(gl_freevalue.valuecode),10,1,0)) over(partition by 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 = '2011'
   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 制单日期,凭证号

(select distinct
       gl_detail.prepareddatev 制单日期,
       gl_voucher.no 凭证号,
       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 = '2011'
   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%' )
 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.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.no) x1,
       sum(decode(length(gl_freevalue.valuecode),10,1,0)) over(partition by 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 = '2011'
   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)