select area_nbr,tel,sub_nbr,purpose from epb_user_tel,epb_tel a where epb_user_tel.epb_tel_id = a.epb_tel_id and epb_user_tel.Epb_User_id = 253 and not exists ( select from EPB_DENIAL_DIAL,EPB_BELONG_TO,EPB_TEL b,EPB_TEL c,EPB_BELONG_TO d,a.epb_tel_id a where EPB_DENIAL_DIAL.source_epbg_id = EPB_BELONG_TO.epbg_id and EPB_BELONG_TO.epb_tel_id = b.epb_tel_id and b.tel = a.tel and b.area_nbr = a.area_nbr and EPB_DENIAL_DIAL.dest_epbg_id = d.epbg_id and d.epb_tel_id = c.epb_tel_id and c.epb_tel_id=a.epb_tel_id )
a.epb_tel_id not in ( select c.epb_tel_id from EPB_DENIAL_DIAL,EPB_BELONG_TO,EPB_TEL b,EPB_TEL c,EPB_BELONG_TO d where EPB_DENIAL_DIAL.source_epbg_id = EPB_BELONG_TO.epbg_id and EPB_BELONG_TO.epb_tel_id =b.epb_tel_id and b.tel = a.tel and b.area_nbr = a.area_nbr and EPB_DENIAL_DIAL.dest_epbg_id = d.epbg_id and d.epb_tel_id = c.epb_tel_id )这里可以优化。 子查询改成反条件,然后用 a.epb_tel_id exist (......)。
from epb_user_tel,epb_tel a
where epb_user_tel.epb_tel_id = a.epb_tel_id
and epb_user_tel.Epb_User_id = 253
and not exists
(
select
from EPB_DENIAL_DIAL,EPB_BELONG_TO,EPB_TEL b,EPB_TEL c,EPB_BELONG_TO d,a.epb_tel_id a
where EPB_DENIAL_DIAL.source_epbg_id = EPB_BELONG_TO.epbg_id
and EPB_BELONG_TO.epb_tel_id = b.epb_tel_id
and b.tel = a.tel and b.area_nbr = a.area_nbr
and EPB_DENIAL_DIAL.dest_epbg_id = d.epbg_id
and d.epb_tel_id = c.epb_tel_id and c.epb_tel_id=a.epb_tel_id
)
(
select c.epb_tel_id
from EPB_DENIAL_DIAL,EPB_BELONG_TO,EPB_TEL b,EPB_TEL c,EPB_BELONG_TO d
where EPB_DENIAL_DIAL.source_epbg_id = EPB_BELONG_TO.epbg_id
and EPB_BELONG_TO.epb_tel_id
=b.epb_tel_id
and b.tel = a.tel and b.area_nbr = a.area_nbr
and EPB_DENIAL_DIAL.dest_epbg_id = d.epbg_id
and d.epb_tel_id = c.epb_tel_id
)这里可以优化。
子查询改成反条件,然后用
a.epb_tel_id exist
(......)。