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是把多个表当成一个视图