select * from
(
select orgname, max(substr(t2.code_version,1)) CODE_VERSION from
(
SELECT tb_cube_fc05.pk_entity pk_org,/*主体pk*/
org_orgs.code orgcode,/*主体编码*/
org_orgs.name orgname,/*主体名称*/
tb_cube_fc05.pk_year pk_year,/*年份*/
tb_cube_fc05.pk_month pk_month,/*月份*/
tb_cube_fc05.pk_year||'-'||tb_cube_fc05.pk_month period,/*期间*/
sum(nvl(tb_cube_fc05.value,0)) totalcost,/*费用总额*/
tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
bd_currtype.code currcode,/*币种编码*/
bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure
LEFT JOIN tb_dataattr tb_dataattr
ON (tb_cube_fc05.pk_mvtype = tb_dataattr.pk_obj)
LEFT JOIN org_dept org_dept
ON (tb_cube_fc05.pk_entity = org_dept.pk_dept)
LEFT JOIN org_orgs org_orgs
ON (tb_cube_fc05.pk_entity = org_orgs.pk_org)
LEFT JOIN bd_defdoc bd_defdoc
ON (tb_cube_fc05.pk_fc01 = bd_defdoc.pk_defdoc)
left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr
where tb_cube_fc05.CODE_MVTYPE='Budget' and tb_dataattr.objname='预算数' and tb_budgetsub.objcode='FC701' --and org_orgs.code='232' --F1169 F1001
-- and substr(tb_cube_fc05.code_version,1) in( select max(substr(code_version,1)) from tb_cube_fc05 )
-- and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
-- tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
group by tb_cube_fc05.pk_entity ,/*主体pk*/
org_orgs.code ,/*主体编码*/
org_orgs.name ,/*主体名称*/
tb_cube_fc05.pk_year ,/*年份*/
tb_cube_fc05.pk_month ,/*月份*/
nvl(tb_cube_fc05.value,0) ,/*费用总额*/
tb_cube_fc05.pk_aimcurr ,/*币种pk*/
bd_currtype.code ,/*币种编码*/
bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
)t2 group by t2.orgname --org_orgs.orgname in (select distinct org_orgs.orgname from org_orgs)
)d1
left join
(
SELECT tb_cube_fc05.pk_entity pk_org,/*主体pk*/
org_orgs.code orgcode,/*主体编码*/
org_orgs.name orgname,/*主体名称*/
tb_cube_fc05.pk_year pk_year,/*年份*/
tb_cube_fc05.pk_month pk_month,/*月份*/
tb_cube_fc05.pk_year||'-'||tb_cube_fc05.pk_month period,/*期间*/
sum(nvl(tb_cube_fc05.value,0)) totalcost,/*费用总额*/
tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
bd_currtype.code currcode,/*币种编码*/
bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure
LEFT JOIN tb_dataattr tb_dataattr
ON (tb_cube_fc05.pk_mvtype = tb_dataattr.pk_obj)
LEFT JOIN org_dept org_dept
ON (tb_cube_fc05.pk_entity = org_dept.pk_dept)
LEFT JOIN org_orgs org_orgs
ON (tb_cube_fc05.pk_entity = org_orgs.pk_org)
LEFT JOIN bd_defdoc bd_defdoc
ON (tb_cube_fc05.pk_fc01 = bd_defdoc.pk_defdoc)
left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr
where tb_cube_fc05.CODE_MVTYPE='Budget' and tb_dataattr.objname='预算数' and tb_budgetsub.objcode='FC701' --and org_orgs.code='232' --F1169 F1001 -- and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
-- tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
group by tb_cube_fc05.pk_entity ,/*主体pk*/
org_orgs.code ,/*主体编码*/
org_orgs.name ,/*主体名称*/
tb_cube_fc05.pk_year ,/*年份*/
tb_cube_fc05.pk_month ,/*月份*/
nvl(tb_cube_fc05.value,0) ,/*费用总额*/
tb_cube_fc05.pk_aimcurr ,/*币种pk*/
bd_currtype.code ,/*币种编码*/
bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
order by org_orgs.name,tb_cube_fc05.pk_month )d2 on d1.orgname=D2.orgname and d1.CODE_VERSION=d2.code_version
另外的解决方案
select * from d2 where d2.orgname|| d2.code_version in
(select r1.orgname|| r1.code_version from
(select ) r1
)
select * from d2 where d2.orgname in
(select r1.orgname from r1)
and d2.code_version in
(select r1.code_version from r1)