如何优化not inselect s_splpacketcode from JC_B_Packet
where
s_splpacketcode not in
(
select p.s_splpacketcode from JC_B_Packet p,jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001'
)
group by s_splpacketcode
order by s_splpacketcode
where
s_splpacketcode not in
(
select p.s_splpacketcode from JC_B_Packet p,jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001'
)
group by s_splpacketcode
order by s_splpacketcode
我只知道这样了....
select s_splpacketcode
from JC_B_Packet
where s_splpacketcode not in
(select p.s_splpacketcode
from JC_B_Packet p, jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001')
--group by s_splpacketcode 去掉此行即可。
order by s_splpacketcode;
where not exists (select 1 from JC_B_Packet p,jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001'
and p.s_splpacketcode = t1.s_splpacketcode)
order by s_splpacketcode
不一定的,光键还是看oracle选择了什么执行计划。所以,楼主请把执行计划帖出来
minus
(
select p.s_splpacketcode from JC_B_Packet p,jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001'
)
order by s_splpacketcode
from JC_B_Packet p
where s_splpacketcode not exists
(select 1 from jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode and t.i_factrunmile != -1 and t.c_taskstate = '04' and t.s_trainsetid = 'kind001'
)
--group by p.s_splpacketcode 没有必要要这一句,因为没有聚合函数
order by p.s_splpacketcode
SELECT S_SPLPACKETCODE
FROM JC_B_PACKET
MINUS (SELECT P.S_SPLPACKETCODE
FROM JC_B_PACKET P, JC_M_REPAIRDATA T
WHERE P.S_SPLPACKETCODE = T.S_TASKCODE
AND T.I_FACTRUNMILE != -1
AND T.C_TASKSTATE = '04'
AND T.S_TRAINSETID = 'kind001')
ORDER BY 1;
--楼主也不反馈些信息。去掉group by 后,若避免s_splpacketcode重复,可以使用distinct。select distinct s_splpacketcode
from JC_B_Packet
where s_splpacketcode not in
(select p.s_splpacketcode
from JC_B_Packet p, jc_m_repairdata t
where p.s_splpacketcode = t.s_taskcode
and t.i_factrunmile != -1
and t.c_taskstate = '04'
and t.s_trainsetid = 'kind001')
order by s_splpacketcode;