1 select * from
2 (3 select orgname, max(substr(t2.code_version,1)) CODE_VERSION from
4 (5 SELECT tb_cube_fc05.pk_entity pk_org,/*主体pk*/
6 org_orgs.code orgcode,/*主体编码*/
7 org_orgs.name orgname,/*主体名称*/
8 tb_cube_fc05.pk_year pk_year,/*年份*/
9 tb_cube_fc05.pk_month pk_month,/*月份*/
10 tb_cube_fc05.pk_year||‘-‘||tb_cube_fc05.pk_month period,/*期间*/
11 sum(nvl(tb_cube_fc05.value,0)) totalcost,/*费用总额*/
12 tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
13 bd_currtype.code currcode,/*币种编码*/
14 bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts15 FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure16 LEFT JOINtb_dataattr tb_dataattr17 ON (tb_cube_fc05.pk_mvtype =tb_dataattr.pk_obj)18 LEFT JOINorg_dept org_dept19 ON (tb_cube_fc05.pk_entity =org_dept.pk_dept)20 LEFT JOINorg_orgs org_orgs21 ON (tb_cube_fc05.pk_entity =org_orgs.pk_org)22 LEFT JOINbd_defdoc bd_defdoc23 ON (tb_cube_fc05.pk_fc01 =bd_defdoc.pk_defdoc)24 left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr25 where tb_cube_fc05.CODE_MVTYPE=‘Budget‘ and tb_dataattr.objname=‘预算数‘ and tb_budgetsub.objcode=‘FC701‘ --and org_orgs.code=‘232‘ --F1169 F1001
26 --and substr(tb_cube_fc05.code_version,1) in( select max(substr(code_version,1)) from tb_cube_fc05 )
27 --and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
28 --tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
29 group by tb_cube_fc05.pk_entity ,/*主体pk*/
30 org_orgs.code ,/*主体编码*/
31 org_orgs.name ,/*主体名称*/
32 tb_cube_fc05.pk_year ,/*年份*/
33 tb_cube_fc05.pk_month ,/*月份*/
34 nvl(tb_cube_fc05.value,0) ,/*费用总额*/
35 tb_cube_fc05.pk_aimcurr ,/*币种pk*/
36 bd_currtype.code ,/*币种编码*/
37 bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts38 )t2 group by t2.orgname --org_orgs.orgname in (select distinct org_orgs.orgname from org_orgs)
39 )d140 left join
41 (42 SELECT tb_cube_fc05.pk_entity pk_org,/*主体pk*/
43 org_orgs.code orgcode,/*主体编码*/
44 org_orgs.name orgname,/*主体名称*/
45 tb_cube_fc05.pk_year pk_year,/*年份*/
46 tb_cube_fc05.pk_month pk_month,/*月份*/
47 tb_cube_fc05.pk_year||‘-‘||tb_cube_fc05.pk_month period,/*期间*/
48 sum(nvl(tb_cube_fc05.value,0)) totalcost,/*费用总额*/
49 tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
50 bd_currtype.code currcode,/*币种编码*/
51 bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts52 FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure53 LEFT JOINtb_dataattr tb_dataattr54 ON (tb_cube_fc05.pk_mvtype =tb_dataattr.pk_obj)55 LEFT JOINorg_dept org_dept56 ON (tb_cube_fc05.pk_entity =org_dept.pk_dept)57 LEFT JOINorg_orgs org_orgs58 ON (tb_cube_fc05.pk_entity =org_orgs.pk_org)59 LEFT JOINbd_defdoc bd_defdoc60 ON (tb_cube_fc05.pk_fc01 =bd_defdoc.pk_defdoc)61 left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr62 where tb_cube_fc05.CODE_MVTYPE=‘Budget‘ and tb_dataattr.objname=‘预算数‘ and tb_budgetsub.objcode=‘FC701‘ --and org_orgs.code=‘232‘ --F1169 F1001
63
64
65
66 --and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
67 --tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
68 group by
69
70 tb_cube_fc05.pk_entity ,/*主体pk*/
71 org_orgs.code ,/*主体编码*/
72 org_orgs.name ,/*主体名称*/
73 tb_cube_fc05.pk_year ,/*年份*/
74 tb_cube_fc05.pk_month ,/*月份*/
75 nvl(tb_cube_fc05.value,0) ,/*费用总额*/
76 tb_cube_fc05.pk_aimcurr ,/*币种pk*/
77 bd_currtype.code ,/*币种编码*/
78 bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts79 order byorg_orgs.name,tb_cube_fc05.pk_month80
81 )d2 on d1.orgname=D2.orgname and d1.CODE_VERSION=d2.code_version