A:
id destmobile orgmobile
1 13845645666
2 13845645666
3 13564853445
4 13564853445
5 13564852555
6 13456456456
7 13456456456
B:
mobile city
1384564 0766
1356485 0745
1345645 0733大家好,在上面两表中,我想根据A左外连接B,条件为:SUBSTR(a.DESTMOBILE,1,7)=b.mobile or SUBSTR(a.ORGMOBILE,1,7)=b.mobile,结果:a.*,b.city请问该SQL要怎样写?
理想结果:
id destmobile orgmobile city
1 13845645666 0766
2 13845645666 0766
3 13564853445 0745
4 13564853445 0745
5 13564852555 0745
6 13456456456 0733
7 13456456456 0733
id destmobile orgmobile
1 13845645666
2 13845645666
3 13564853445
4 13564853445
5 13564852555
6 13456456456
7 13456456456
B:
mobile city
1384564 0766
1356485 0745
1345645 0733大家好,在上面两表中,我想根据A左外连接B,条件为:SUBSTR(a.DESTMOBILE,1,7)=b.mobile or SUBSTR(a.ORGMOBILE,1,7)=b.mobile,结果:a.*,b.city请问该SQL要怎样写?
理想结果:
id destmobile orgmobile city
1 13845645666 0766
2 13845645666 0766
3 13564853445 0745
4 13564853445 0745
5 13564852555 0745
6 13456456456 0733
7 13456456456 0733
id destmobile orgmobile
b
id destmobile orgmobile cityselect a.id,a.destmobile ,a.orgmobile ,b.city
from
(select id ,destmobile, orgmobile, substr(destmobile,1,7) a_destmobile from a) table_a,
b
where table_a.a_destmobile = b.destmobile(+);
这样就没问题了 显示结果 b.city 可能会有 null 出现
A:
id destmobile orgmobile
1 13845645666
2 空 13845645666
3 13564853445
4 空 13564853445
5 13564852555
6 空 13456456456
7 13456456456
where nvl(substr(destmobile,1,7),substr(orgmobile,1,7))=b.mobile
select
a.id ,
a.destmobile ,
b.orgmobile ,
b.city
from A left join
B on SUBSTR(a.DESTMOBILE,1,7)=b.mobile