请教SQL优化

[smartlylife]

我的意图是从Sys_Commoncodedata表中取到三个汉字，这三个汉字是可以是同音字
的，Sys_Commoncodedata表中有同音字库在里面的，现在就是给三个拼音后找出
Sys_Commoncodedata字库中所有可以匹配的同音字的名字并与inner_guest表中的
cust_name字段值相同

我的
sql水平很低，谁可以帮我优化一下sql语句，Sys_Commoncodedata 数据量在3万
条左右,inner_guest数据量以20万条估计

先谢了

select i.cust_name from inner_guest i where i.cust_name
in ( select a0.data_Name + a1.data_Name + a2.data_Name from
Sys_Commoncodedata a0, Sys_Commoncodedata a1,
Sys_Commoncodedata a2 where a0.code_No like '19' and a0.data_No
like 'wang' and a1.code_No like '19' and a1.data_No like 'xiang' and
a2.code_No like '19' and a2.data_No like 'hua')

[zjcxc]

select i.cust_name
from inner_guest i
where
i.cust_name in (
select a0.data_Name + a1.data_Name + a2.data_Name
from Sys_Commoncodedata a0, Sys_Commoncodedata a1, Sys_Commoncodedata a2
where a0.code_No = '19'
and a0.data_No = 'wang'
and a1.code_No = '19'
and a1.data_No = 'xiang'
and a2.code_No = '19'
and a2.data_No = 'hua')


--或者:
select i.cust_name
from inner_guest i
where exists(
select *
from Sys_Commoncodedata a0, Sys_Commoncodedata a1, Sys_Commoncodedata a2
where a0.code_No = '19'
and a0.data_No = 'wang'
and substring(i.cust_name,1,1)=a0.data_Name
and a1.code_No = '19'
and a1.data_No = 'xiang'
and substring(i.cust_name,2,1)=a1.data_Name
and a2.code_No = '19'
and a2.data_No = 'hua'
and substring(i.cust_name,3,1)=a1.data_Name)

[zjcxc]

--或者:

select i.cust_name
from inner_guest i
where (
select count(distinct data_Name)
from Sys_Commoncodedata
where code_No = '19' and(
data_No = 'wang' and data_Name=substring(i.cust_name,1,1)
or
data_No = 'xiang' and data_Name=substring(i.cust_name,2,1)
or
data_No = 'hua' and data_Name=substring(i.cust_name,3,1)))=3

[xluzhong]

select i.cust_name
from inner_guest i
where exists(
select *
from Sys_Commoncodedata a0, Sys_Commoncodedata a1, Sys_Commoncodedata a2
where a0.code_No = '19'
and a0.data_No = 'wang'
and substring(i.cust_name,1,1)=a0.data_Name
and a1.code_No = '19'
and a1.data_No = 'xiang'
and substring(i.cust_name,2,1)=a1.data_Name
and a2.code_No = '19'
and a2.data_No = 'hua'
and substring(i.cust_name,3,1)=a1.data_Name)

[子陌红尘]

select
i.cust_name
from
inner_guest i
where
len(i.cust_name) = 3
and
exists(select
1
from
Sys_Commoncodedata
where
code_No like '19'
and
data_No like 'wang'
and
data_Name = substring(i.cust_name,1,1))
and
exists(select
1
from
Sys_Commoncodedata
where
code_No like '19'
and
data_No like 'xiang'
and
data_Name = substring(i.cust_name,2,1))
and
exists(select
1
from
Sys_Commoncodedata
where
code_No like '19'
and
data_No like 'hua'
and
data_Name = substring(i.cust_name,3,1))

[smartlylife]

已加分了
select i.cust_name
from inner_guest i
where (
select count(distinct data_Name)
from Sys_Commoncodedata
where code_No = '19' and(
data_No = 'wang' and data_Name=substring(i.cust_name,1,1)
or
data_No = 'xiang' and data_Name=substring(i.cust_name,2,1)
or
data_No = 'hua' and data_Name=substring(i.cust_name,3,1)))=3
速度最快
select i.cust_name
from inner_guest i
where
i.cust_name in (
select a0.data_Name + a1.data_Name + a2.data_Name
from Sys_Commoncodedata a0, Sys_Commoncodedata a1, Sys_Commoncodedata a2
where a0.code_No = '19'
and a0.data_No = 'wang'
and a1.code_No = '19'
and a1.data_No = 'xiang'
and a2.code_No = '19'
and a2.data_No = 'hua')
慢一点，另两个数据没查对，还没仔细看，先谢了先

[smartlylife]

libin_ftsafe(子陌红尘) 对不起，我回复的时候还没看到你的，现在加不了分了:( 谢谢你