求一个 exists() 的替代写法

Question

select tel_num
from   t_tmp_tel a
where left(from_tel,1)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,2)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,3)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,4)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,5)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,6)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,7)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,8)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,9)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,10)not in
(select tel_prefix from t_tmp_prefix)
and left(from_tel,11)not in
(select tel_prefix from t_tmp_prefix)
这样写也比你本来的快

Answer 1

这样试试:判断是否在前缀表
select tel_num
from   t_tmp_tel a
where  exists(select count(*) from t_tmp_prefix where a.from_tel like tel_prefix+'%')不在前缀表
select tel_num
from   t_tmp_tel a
where  not exists(select count(*) from t_tmp_prefix where a.from_tel like tel_prefix+'%')

Answer 2

判断是否在前缀表
select tel_num
from   t_tmp_tel a
where  exists(select * from t_tmp_prefix where a.from_tel like tel_prefix+'%')可以改寫下Select A.tel_num
from   t_tmp_tel a
Inner Join t_tmp_prefix B
On A.tel_num like B.tel_prefix+'%'