select b1.*,a1.* from a as a1, b as b1 where b1.callednumber like a1.areacode||'%' and not exists(select * from a where b1.callednumber like a.areacode||'%' and length(a.areacode)>length(a1.areacode))
select a.* from a,b where a.areacode = substr(b.callednumber,1,length(b.callednumber)-6)
SQL> select CALLEDNUMBER, AREACODE from ( 2 select CALLEDNUMBER, AREACODE, DESTINATION, len, 3 row_number() over(partition by CALLEDNUMBER order by LEN desc) id 4 from ( 5 select CALLEDNUMBER, AREACODE, DESTINATION, 6 length(b.callednumber)-length(ltrim(replace(b.callednumber, a.areacode, ' '))) len 7 from b, a 8 )) 9 where id=1;CALLEDNUMBER AREACODE -------------------- -------------------- 00112888888 00112 0011888888 0011 001888888 001
select a.*,b.* from a,b where a.areacode='local*' and substr(b.callednumber,1,1)<>'0' union all select a.*,b.* from a,b where a.areacode='0*' and substr(b.callednumber,1,1)='0' and substr(b.callednumber,2,1)<>'0' union all select b1.*,a1.* from a as a1, b as b1 where b1.callednumber like a1.areacode||'%' and substr(b1.callednumber,1,2)='00' and not exists(select * from a where b1.callednumber like a.areacode||'%' and length(a.areacode)>length(a1.areacode))
select callednumber,nvl(max(areacode),decode(substr(callednumber,1,1),'0',decode(substr(callednumber,2,1),'0','00*','0*'),'local*')) from a,b where b.callednumber like a.areacode(+)||'%' group by b.callednumber 这里要求'local*','0*','00*'为固定值。 如果不固定,则应有其他判断
b1.*,a1.*
from
a as a1,
b as b1
where
b1.callednumber like a1.areacode||'%'
and
not exists(select *
from a
where
b1.callednumber like a.areacode||'%'
and
length(a.areacode)>length(a1.areacode))
from a,b
where a.areacode = substr(b.callednumber,1,length(b.callednumber)-6)
2 select CALLEDNUMBER, AREACODE, DESTINATION, len,
3 row_number() over(partition by CALLEDNUMBER order by LEN desc) id
4 from (
5 select CALLEDNUMBER, AREACODE, DESTINATION,
6 length(b.callednumber)-length(ltrim(replace(b.callednumber, a.areacode, ' '))) len
7 from b, a
8 ))
9 where id=1;CALLEDNUMBER AREACODE
-------------------- --------------------
00112888888 00112
0011888888 0011
001888888 001
假设a表有记录:(数据都是假设的)
areacode destination 费率
local* 市内 2
0* 国内 5
00* 其它国际 35
001 美国 10
0011 美国手机 20
00112 爪哇岛 15
00852 香港 12
008521 香港手机 10b表:
callednumber
5878975
07102124546
00188888812
00118888883
0011288888823423
0085290114
008521698
现在要求以“非0”开头的记录,匹配“local* 市内 2”记录,以“一个0”开头的匹配
“0* 国内 5”记录,以“00”开头的先匹配最长的记录,比如,对于b表中的记录001888888要找到a表中的记录“001 美国 10”;而对于记录00112888888要求找到a表中的“00112 爪哇岛 15”,即要根据最长的前缀来匹配,但是如果没有匹配到,就以“00* 其它国际 35”匹配。有没有什么好一点的方法?速度快一点的。不好意思,一开始我没有考虑清除。
谢谢各位!
a.*,b.*
from
a,b
where
a.areacode='local*' and substr(b.callednumber,1,1)<>'0'
union all
select
a.*,b.*
from
a,b
where
a.areacode='0*' and substr(b.callednumber,1,1)='0' and substr(b.callednumber,2,1)<>'0'
union all
select
b1.*,a1.*
from
a as a1,
b as b1
where
b1.callednumber like a1.areacode||'%'
and
substr(b1.callednumber,1,2)='00'
and
not exists(select *
from a
where
b1.callednumber like a.areacode||'%'
and
length(a.areacode)>length(a1.areacode))
这里要求'local*','0*','00*'为固定值。
如果不固定,则应有其他判断