update
cmcs_report_pm_traffic t
set (t.cell,t.is_check) =
(
select rack,1 from (
select rack,i.bsc_code,i.bts_port,t2.bts_code,
row_number()over(partition by bsc||bts order by id)rr
from cmcs_alarm_res_info i,cmcs_report_pm_traffic t2
where i.bsc_code=t2.bsc
and i.bts_port = t2.bts
)where rr=1 and bsc_code=t.bsc and bts_port=t.bts
)
where t.id not in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack = t1.cell
)
and t.id in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
)
cmcs_report_pm_traffic t
set (t.cell,t.is_check) =
(
select rack,1 from (
select rack,i.bsc_code,i.bts_port,t2.bts_code,
row_number()over(partition by bsc||bts order by id)rr
from cmcs_alarm_res_info i,cmcs_report_pm_traffic t2
where i.bsc_code=t2.bsc
and i.bts_port = t2.bts
)where rr=1 and bsc_code=t.bsc and bts_port=t.bts
)
where t.id not in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack = t1.cell
)
and t.id in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
)
NOT EXISTS 代替 NOT IN update
cmcs_report_pm_traffic t
set (t.cell,t.is_check) =
(
select rack,1 from (
select rack,i.bsc_code,i.bts_port,t2.bts_code,
row_number()over(partition by bsc||bts order by id)rr
from cmcs_alarm_res_info i,cmcs_report_pm_traffic t2
where i.bsc_code=t2.bsc
and i.bts_port = t2.bts
)where bsc_code=t.bsc and bts_port=t.bts and rr=1)
where t.id not exists(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where t1.id = t.id
and r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack = t1.cell
)
and t.id exists(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where t1.id = t.id
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
)
where t.id not in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack = t1.cell
)
and t.id in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
)
改进后的代码:where t.id in(
select t1.id
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack <> t1.cell
)
如果数据量大的话,可以再用一下exists改进:
where exists(
select 1
from
cmcs_alarm_res_info r,
cmcs_report_pm_traffic t1
where
r.bsc_code=t1.bsc
and r.bts_port = t1.bts
and r.rack <> t1.cell
and t1.id=t.id)
cmcs_report_pm_traffic t1 两张表的数据量,因为 = 值相对来说效率是最高的,<> 可根据数据量的大小来灵活选择