insert into T_A(id, code, name) select T_B.code, T_B.name, T_C.id

[xuxingguang]

有一个表T_A,字段包括
id code name

T_A的code, name来自T_B; T_A的id来自T_C的id,而且T_C的id由T_B的name2确定。

请教各位大侠，这个sql怎么写？

[xuxingguang]

表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).

[starcomplex]

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(+)

[xcluii]

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(+)

[ljhcy99]

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)

[Mugooxin]

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;
楼主可以试试，是不是这样的！

[catslee]

就把几个表关联起来咯~~!!
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(+)

[seathj]

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(+)