现在有这样的一对数据
表A
name address
123456 jlkjsdilmdskl
987654 sadsaff
456789 afdfdsfds
表B
name
12345
98765
45678我想把表A 通过表B的name值 中的name截取出来就剩
name
6
4
9
用什么办法好呢。。数据有数万条。。必须要这么截才行。
表A
name address
123456 jlkjsdilmdskl
987654 sadsaff
456789 afdfdsfds
表B
name
12345
98765
45678我想把表A 通过表B的name值 中的name截取出来就剩
name
6
4
9
用什么办法好呢。。数据有数万条。。必须要这么截才行。
from (select substr(t1.name,1,5) subname, t1.name name from t1) a, t2 b
where a.subname=b.name看看这个能否满足你的要求
with a as (select 123456 name,'jlkjsdilmdskl' adress from dual
union all
select 987654,'sadsaff' from dual
union all
select 456789,'afdfdsfds' from dual),
b as (select 12345 name from dual
union all
select 98765 name from dual
union all
select 45678 name from dual
)
select replace(c.name, d.name)
from (select a.*, rownum rn from a) c, (select b.*, rownum rn from b) d
where c.rn = d.rn;
我心里的理想方法就是 X=A.name-B.name 得到一组新的数据 但用ORACLE怎么能分条的进行字符串的相减呢
字段长度是个不定值,但总体,就是想A.name-B.name 然后对分别对 相减后的结果进行判断,相减后的字段是否为数字。
(select 123456 name, 'jlkjsdilmdskl' adress
from dual
union all
select 987654, 'sadsaff'
from dual
union all
select 456789, 'afdfdsfds' from dual),
b as
(select 12345 name
from dual
union all
select 98765 name
from dual
union all
select 45678 name from dual)
select replace(name, subname)
from (select t.name, t1.name as subname, instr(t.name, t1.name) as cn
from a t, b t1)
where cn = 1
from (select t.name, t1.name as subname, instr(t.name, t1.name) as cn
from a t, b t1)
where cn = 1
from A,B
where B.rn = A.rn;
表A 1234567890 表B 123456 得到 7890 类似这样的。表C 7890
0987654321 0987 654321 654321
9999222233 99992 22233 1111
11122 1112 2 3333
最后对这一列进行判断是否为数字,把为数字的取出,与表C通过ID条件连接 对比表C的字段是否相等。最后把不相等的列出,将等到的数据 UPDATE到表C 的相对应的字段当中(最后把表C中 1111,3333更新成22233,2)有点复杂。。哈哈