给你看看我练习的oracle语句

  1 -------预算--
  2 CREATE OR REPLACE VIEW V_YUSUAN_BGY_WZ20151204 AS
  3 SELECT tb_cube_fc05.pk_entity  pk_org,/*主体pk*/
  4        org_orgs.code orgcode,/*主体编码*/
  5        org_orgs.name orgname,/*主体名称*/
  6        tb_cube_fc05.pk_year    pk_year,/*年份*/
  7        tb_cube_fc05.pk_month   pk_month,/*月份*/
  8        tb_cube_fc05.pk_year||‘-‘||tb_cube_fc05.pk_month period,/*期间*/
  9        sum(nvl(tb_cube_fc05.value,0))  totalcost,/*费用总额*/
 10        tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
 11        bd_currtype.code currcode,/*币种编码*/
 12        bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
 13   FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure
 14   LEFT JOIN tb_dataattr tb_dataattr
 15     ON (tb_cube_fc05.pk_mvtype = tb_dataattr.pk_obj)
 16   LEFT JOIN org_dept org_dept
 17     ON (tb_cube_fc05.pk_entity = org_dept.pk_dept)
 18   LEFT JOIN org_orgs org_orgs
 19     ON (tb_cube_fc05.pk_entity = org_orgs.pk_org)
 20   LEFT JOIN bd_defdoc bd_defdoc
 21     ON (tb_cube_fc05.pk_fc01 = bd_defdoc.pk_defdoc)
 22   left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr  
 23   where  tb_cube_fc05.CODE_MVTYPE=‘Budget‘ and tb_dataattr.objname=‘预算数‘ and tb_budgetsub.objname=‘管理（口径）费用总额‘  and org_orgs.code=‘F1001‘  --F1169   F1001
 24   and  substr(tb_cube_fc05.code_version,1) in( select max(substr(code_version,1)) from tb_cube_fc05  )
 25  -- and exists(select ts from (select max(ts) ts from tb_cube_fc05  group by tb_cube_fc05.pk_year    ,/*年份*/
 26  --    tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
 27   group by tb_cube_fc05.pk_entity  ,/*主体pk*/
 28        org_orgs.code ,/*主体编码*/
 29        org_orgs.name ,/*主体名称*/
 30        tb_cube_fc05.pk_year    ,/*年份*/
 31        tb_cube_fc05.pk_month   ,/*月份*/
 32        nvl(tb_cube_fc05.value,0) ,/*费用总额*/
 33        tb_cube_fc05.pk_aimcurr ,/*币种pk*/
 34        bd_currtype.code ,/*币种编码*/
 35        bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
 36 
 37   ---费用调整单
 38 create or replace view v_fyadjust_byg_wz as
 39 select distinct zh.djlxbm,/*费用类型编码*/
 40 tb.billtypename,/*费用类型名称*/
 41 zh.bzbm,/*币种pk*/
 42 cu.name bzname,/*币种名称*/
 43 z.assume_org,/*组织主体pk*/
 44 og.name orgname,/*组织主体name*/
 45 z.assume_amount,/*承担金额*/
 46 z.ysdate,/*费用入账日期*/
 47 z.defitem16,/*费用类型*/
 48 z.defitem27 from er_cshare_detail z inner join er_bxzb zh on z.pk_jkbx=zh.pk_jkbx
 49 left join bd_billtype tb on tb.pk_billtypecode=zh.djlxbm and nvl(tb.dr,0)=0
 50 left join bd_currtype cu on cu.pk_currtype=zh.bzbm and nvl(cu.dr,0)=0
 51 left join org_orgs og on og.pk_org=z.assume_org and nvl(og.dr,0)=0
 52 where zh.djlxbm in(‘264a‘,‘264X-Cxx-0009‘) and nvl(z.dr,0)=0 and nvl(zh.dr,0)=0;
 53 
 54 
 55 ---采购发票
 56 create or replace view v_po_invoice_bgy_wz as
 57 select pi.pk_org,/*主体pk*/
 58 og.name ogrname,/*主体名称*/
 59  pi.pk_busitype,/*业务流程pk*/
 60        bt.businame,/*业务流程名称*/
 61        pi.dbilldate,/*单据日期*/
 62        sum(nvl(pib.norigtaxmny,0)) norigtaxmny,/*价税合计金额*/
 63        pi.corigcurrencyid,/*币种pk*/cy.name curryname,/*币种名称*/
 64        pi.vmemo,
 65        --pib.vfirstcode,/*来源单据号*/
 66        pi.taudittime provedate,/*审批日期*/
 67        pib.vbdef2 costtype, /*费用类型*/
 68        pib.vbdef3 applydetp /*请购部门*/
 69   from po_invoice pi
 70  inner join po_invoice_b pib
 71     on pi.pk_invoice = pib.pk_invoice
 72    and nvl(pi.dr, 0) = 0
 73    and nvl(pib.dr, 0) = 0
 74    inner join org_orgs og on og.pk_org=pi.pk_org
 75   left join bd_busitype bt
 76     on bt.pk_busitype = pi.pk_busitype
 77     left join bd_currtype cy on cy.pk_currtype=pi.corigcurrencyid
 78    and nvl(bt.dr, 0) = 0
 79    group by pi.pk_org,og.name , pi.pk_busitype,
 80        bt.businame,
 81        pi.corigcurrencyid,cy.name,
 82        pi.vmemo,
 83        --pib.vfirstcode,
 84         pi.taudittime,
 85        pib.vbdef2, pi.dbilldate,
 86        pib.vbdef3;
 87 
 88 
 89 
 90 
 91 ---采购订单 
 92 create or replace view v_po_order_bgy_wz as
 93 select po.pk_busitype,/*业务流程pk*/
 94 bt.businame,/*业务流程名称*/
 95 po.pk_dept,/*采购部门pk*/
 96 de.name deptname,/*采购部门名称*/
 97 de.pk_org,/*采购主体pk*/
 98 og.name orgname, /*采购主体名称*/
 99 pb.vbdef3/*费用类型*/,
100 pb.norigtaxmny,/*价税合计*/
101 po.vmemo,
102 --pb.vsourcecode,/*来源单号*/
103 po.taudittime /*审批日期*/
104 from po_order po inner join po_order_b pb on po.pk_order=pb.pk_order and nvl(po.dr,0)=0 and nvl(pb.dr,0)=0
105 left join org_dept de on de.pk_dept=po.pk_dept and nvl(de.dr,0)=0
106 left join bd_busitype bt on bt.pk_busitype=po.pk_busitype and nvl(bt.dr,0)=0
107 left join org_orgs og on og.pk_org=de.pk_org and nvl(og.dr,0)=0;