表T_A: (空表) id code name表T_B: (原始表) code name name2表T_C: (原始表) id name2 ---------------------------------- T_A的id,code,name分别来自于T_C.id,T_B.code,T_B.name。 规则是:如果T_B中,一条记录的name2的值能够在T_C的name2中找到,那么就将此对应的T_C.id放入到T_A.id;如果找不到,那么此条记录在T_A中应该是(NULL,T_B.code, T_B.name).
insert into t_a(id, code, name, c_id, d_num ) select t_a_sequence.nextval,b.code,b.name,c.c_id,d.d_num from t_b b,t_c c,(select b1.name name,d1.d_num d_num from t_b b1,t_d d1 where b1.name3=d1.name3) d where b.name=d.name(+) and b.name2=c.name2(+)
insert into t_a(id, code, name) select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d where b.name2 = d.name2(+)
insert into 表T_A(id,code,name) select T_C.id,T_B.code,T_B.name from T_C left outer join T_B on(T_B.name2=T_C.name2)
insert into ta(id,code,name) (select (selcect tc.id from tc where tc.name=tb.name) id, tb.code,tb.name from tb)
CREATE TABLE T_A(ID VARCHAR2(2), CODE VARCHAR2(50), NAME VARCHAR2(50)); CREATE TABLE T_B(CODE VARCHAR2(50), NAME VARCHAR2(50), NAME2 VARCHAR2(50)); CREATE TABLE T_C(ID VARCHAR2(2), NAME2 VARCHAR2(50)); COMMIT;INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code1','name1','张三'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code2','name2','李四'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code3','name3','王五'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code4','name4','小无'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code5','name5','小的'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code6','name6','小了'); INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code7','name7','小批'); COMMIT;
INSERT INTO T_C(ID,NAME2) VALUES('1','张三'); INSERT INTO T_C(ID,NAME2) VALUES('2','李四'); INSERT INTO T_C(ID,NAME2) VALUES('3','王五'); INSERT INTO T_C(ID,NAME2) VALUES('4','小王'); INSERT INTO T_C(ID,NAME2) VALUES('5','小张'); COMMIT; INSERT INTO T_A SELECT B.ID,A.CODE,A.NAME FROM T_B A INNER JOIN T_C B ON A.NAME2=B.NAME2(+); COMMIT; 楼主可以试试,是不是这样的!
就把几个表关联起来咯~~!! insert into t_a(id, code, name) select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d where b.name2 = d.name2(+)
insert into t_a(id, code, name) select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d where b.name2 = d.name2(+)
id code name表T_B: (原始表)
code name name2表T_C: (原始表)
id name2
----------------------------------
T_A的id,code,name分别来自于T_C.id,T_B.code,T_B.name。
规则是:如果T_B中,一条记录的name2的值能够在T_C的name2中找到,那么就将此对应的T_C.id放入到T_A.id;如果找不到,那么此条记录在T_A中应该是(NULL,T_B.code, T_B.name).
code,
name,
c_id,
d_num
)
select t_a_sequence.nextval,b.code,b.name,c.c_id,d.d_num
from t_b b,t_c c,(select b1.name name,d1.d_num d_num from t_b b1,t_d d1 where b1.name3=d1.name3) d
where b.name=d.name(+) and b.name2=c.name2(+)
insert into t_a(id, code, name)
select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d
where b.name2 = d.name2(+)
select T_C.id,T_B.code,T_B.name
from T_C left outer join T_B
on(T_B.name2=T_C.name2)
(select (selcect tc.id from tc where tc.name=tb.name) id, tb.code,tb.name from tb)
CREATE TABLE T_B(CODE VARCHAR2(50), NAME VARCHAR2(50), NAME2 VARCHAR2(50));
CREATE TABLE T_C(ID VARCHAR2(2), NAME2 VARCHAR2(50));
COMMIT;INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code1','name1','张三');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code2','name2','李四');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code3','name3','王五');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code4','name4','小无');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code5','name5','小的');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code6','name6','小了');
INSERT INTO T_B(CODE,NAME,NAME2) VALUES ('code7','name7','小批');
COMMIT;
INSERT INTO T_C(ID,NAME2) VALUES('1','张三');
INSERT INTO T_C(ID,NAME2) VALUES('2','李四');
INSERT INTO T_C(ID,NAME2) VALUES('3','王五');
INSERT INTO T_C(ID,NAME2) VALUES('4','小王');
INSERT INTO T_C(ID,NAME2) VALUES('5','小张');
COMMIT; INSERT INTO T_A SELECT B.ID,A.CODE,A.NAME FROM T_B A INNER JOIN T_C B ON A.NAME2=B.NAME2(+);
COMMIT;
楼主可以试试,是不是这样的!
insert into t_a(id, code, name)
select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d
where b.name2 = d.name2(+)
select c.id, b.code, b.name from t_b b, t_c c, (select from t_b, t_c where t_b.name2=t_c.name2) d
where b.name2 = d.name2(+)