写报表分组有那么笛卡尔积中限定的关联条件必须都有



select AA.pk_projectinfo,
       AA.project_name,
       AA.second_classify,
       AA.matclasschild,
      AA.org_name ,
      '2018-04' as plantime,
       AA.total_count,
       AA.dlcount,AA.dscount,AA.arcount,AA.cscount,AA.spcount from (
select a.pk_projectinfo,
       a.project_name,
       a.second_classify,
       a.matclasschild,
      a.org_name ,
       b.total_count,
       b.dlcount,b.dscount,b.arcount,b.cscount,b.spcount
        from  (          select distinct d.project_name,d.pk_projectinfo,h.itemname second_classify,d.matclasschild,d.belong_unit_name as org_name from  (select distinct d1.project_name,d1.matclasschild,d1.pk_projectinfo,d1.belong_unit_name from  CON_CONTRACT d1) d
      
  left join bas_dataitem h
       on h.itemcode = d.matclasschild where   h.dictcode = 'Classify2'  order by d.pk_projectinfo asc   ) a  left join

(

select ct.pk_projectinfo,
       ct.project_name,
       bd.itemname second_classify,
      ct.belong_unit_name as org_name ,
      
      
    
       (select sum(plmd.count)
          from PLAN_MONTH plm
          join PLAN_MONTHDETAIL plmd
            on plm.pk_monthplan = plmd.pk_monthplan
         where plm.propk = ct.pk_projectinfo
         and plm.second_classify=ct.matclasschild
         and plm.PK_SALE_CONTRACT in (select ct1.pk_contract from  CON_CONTRACT ct1  where ct1.belong_unit_name=ct.belong_unit_name )
         and plm.status='3'
       
         and  plm.plantime like to_date('2018-04','yyyy-MM')
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as total_count,
       (select sum(dld.count)
         from sto_delivery dl
          join sto_deliverydetail dld
            on dl.pk_sto_delivery = dld.pk_sto_delivery
         where dl.pk_project = ct.pk_projectinfo
          and dl.materialsclassify=ct.matclasschild
   and dl.PK_SALE_CONTRACT in (select ct1.pk_contract from  CON_CONTRACT ct1  where ct1.belong_unit_name=ct.belong_unit_name )
          and (dl.status='3' or dl.status='5')
          and to_char(dl.TAKETIME,'yyyy-MM') like '2018-04'
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as dlcount,
       (select sum(dsd.disp_num)
         from STO_DISPATCH ds
          join sto_dispatchdetail dsd
            on ds.pk_sto_dispatch = dsd.pk_sto_dispatch
         where ds.pk_project = ct.pk_projectinfo
          and ds.materialsclassify=ct.matclasschild
            and ds.STO_DELIVERY_CODE in (select dl2.delivery_code from  CON_CONTRACT ct1 join  sto_delivery dl2 on dl2.pk_project = ct1.pk_projectinfo
          where dl2.materialsclassify=ct1.matclasschild and ct1.belong_unit_name=ct.belong_unit_name）
         and ds.status='3'
          and to_char(ds.DISPATCHTIME,'yyyy-MM') like '2018-04'
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as dscount,
       (select sum(ard.ARRIVED_NUM)
          from STO_ARRIVED ar
          join sto_arriveddetail ard
            on ar.PK_STO_ARRIVED = ard.PK_STO_ARRIVED
         where ar.pk_project = ct.pk_projectinfo
          and ar.materialsclassify=ct.matclasschild
           and ar.STO_DELIVERY_CODE in (select dl2.delivery_code from  CON_CONTRACT ct1 join  sto_delivery dl2 on dl2.pk_project = ct1.pk_projectinfo
          where dl2.materialsclassify=ct1.matclasschild and ct1.belong_unit_name=ct.belong_unit_name）
          and ar.status='3'
           and to_char(ar.ARRIVEDTIME,'yyyy-MM') like  '2018-04'
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as arcount,
       (select sum(REAL_NUM)
         from FIN_CLEARSHEET cs
          join fin_clearsheet_detail csd
            on cs.pk_clearsheet = csd.pk_clearsheet
         where cs.pk_projectinfo = ct.pk_projectinfo
          and cs.second_classify=ct.matclasschild
          and cs.PK_CONTRACT in (select ct1.pk_contract from  CON_CONTRACT ct1  where ct1.belong_unit_name=ct.belong_unit_name )
         and cs.status='3'
         and to_char(cs.SE_ENDTIME,'yyyy-MM') like  '2018-04'
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as cscount,
       (select sum(REAL_NUM)
         from FIN_SUPACCOUNT sp
          join fin_supaccount_detail spd
            on sp.pk_supaccount = spd.pk_supaccount
         where sp.pk_projectinfo = ct.pk_projectinfo
          and  sp.second_classify=ct.matclasschild
           and sp.CONTRACT_CODE in (select cpc1.MASTER_CODE from  CON_PUR_CONTRACT cpc1  where cpc1.BELONG_UNIT_NAME=ct.belong_unit_name )
         and sp.status='3'
         and to_char(sp.SE_ENDTIME,'yyyy-MM') like  '2018-04'
         group by ct.pk_projectinfo, bd.itemname,ct.matclasschild,ct.belong_unit_name) as spcount

  from  CON_CONTRACT ct 
 
  left join bas_dataitem bd
       on bd.itemcode = ct.matclasschild
 where
  
  ct.contract_status='3'
 
   and bd.dictcode = 'Classify2'
  
  

  
 group by ct.pk_projectinfo, ct.project_name, bd.itemname,ct.matclasschild,ct.belong_unit_name
 order by ct.pk_projectinfo asc,ct.belong_unit_name asc

) b on a.pk_projectinfo=b.pk_projectinfo and a.second_classify= b.second_classify     and a.org_name=b.org_name  order by   b.org_name,b.pk_projectinfo asc,b.org_name asc
) AA where   AA.matclasschild like '%%'
   and AA.project_name like  '%成都%'
 
   and AA.org_name like  '%%'
order by AA.org_name desc,AA.pk_projectinfo desc