业务场景,现有3 w条账号,找到账号是这3 w条账号的数据,
1.建立临时表
create table TEMP_ACCOUNTNUMS ACCOUNTNUM VARCHAR2(64)not null
2.用nodepad++列块编辑功能将3 w条账号处理为insert语句,insert all
into TEMP_ACCOUNTNUMS("ACCOUNTNUM")values(123456789)
into TEMP_ACCOUNTNUMS("ACCOUNTNUM")values(987456123)
select 1 from dual;Commit;
3.开始查询
Select BIZID from accountcheck_apply__info a where a.accountnum in(select ta.accountnum from temp_accountnums ta where a.accountnum like '%'||ta.accountnum||'%')
考虑到like只有右边有%时才走索引,所以将like换成instr
Select BIZID from accountcheck_apply__info a where accountnum in(select ta.accountnum from temp_accountnums ta where instr(a.accountnum,ta.accountnum)>0)
错误方案
select distinct bizid from accountcheck_apply_info a left ioin temp_accountnums ta on a.accountnum like '%'||ta.accountnum||'%'
select distinct bizid from temp_accountnums ta left ioin accountcheck_apply_info a on a.accountnum like '%'||ta.accountnum||'%'
上述方案错误原因为多表联查不管右连接还是左连接,肯定会有一个表是查询全部数据的,不能保证查询出数据是全部符合要求的。