oracle with语句

with zte as(
select QaContractIdCont conid
from dms_dmsnew.emis_syn_boadrinfo bd
join dms_dmsnew.emis_conheaderinfo ec
on bd.contract_header_id = ec.contract_header_id
left join dms_dmsnew.EMIS_REF_CONTICONTRACT erc
on bd.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
left join v_maintain m
on erc.emis_ref_conti_header_id = m.emis_ref_conti_header_id
and m.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
where 1 = 1
and upper(ec.contract_number) like '%2016%'

union all

select OmContractIdCont conid
from dms_dmsnew.emis_syn_boadrinfo bd
join dms_dmsnew.emis_conheaderinfo ec
on bd.contract_header_id = ec.contract_header_id
left join dms_dmsnew.EMIS_REF_CONTICONTRACT erc
on bd.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
left join v_maintain m
on erc.emis_ref_conti_header_id = m.emis_ref_conti_header_id
and m.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
where 1 = 1
and upper(ec.contract_number) like '%2016%'

union all

select SpContractIdCont conid
from dms_dmsnew.emis_syn_boadrinfo bd
join dms_dmsnew.emis_conheaderinfo ec
on bd.contract_header_id = ec.contract_header_id
left join dms_dmsnew.EMIS_REF_CONTICONTRACT erc
on bd.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
left join v_maintain m
on erc.emis_ref_conti_header_id = m.emis_ref_conti_header_id
and m.emis_syn_boadrinfo_id = erc.emis_syn_boadrinfo_id
where 1 = 1
and upper(ec.contract_number) like '%2016%')

select t.contract_header_id id,t.contract_number no
from emis_conheaderinfo t
where 1 = 1
and exists (select 1 from zte where conid=t.contract_header_id)

with是把多个表当成一个视图

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!