多表关联查询，选择字段插入新表，该怎么做？

[conroy2008]

create table seller1
(
id number,uname varchar2(30),sfzh varchar2(30),create_time varchar2(30),product_id varchar2(30)
);
insert into seller1 values (1,'张1','330100198511223569','20110101','2');
insert into seller1 values (2,'李四','330100198511223569','20120301','1');
insert into seller1 values (3,'王红','420100198411223569','20020101','3');
insert into seller1 values (4,'张三','410100198111228842','20130101','3');
commit;

create table seller2
(
id number,uname varchar2(30),sfzh varchar2(30),contact_way varchar2(30),sex varchar2(30),create_time varchar2(30),dept varchar2(30),product_id varchar2(30)
);

insert into seller2 values (1,'李四','330100198511223569','12345678','1','20120301','10','1');
insert into seller2 values (2,'李五','330100198511223333','12345678','0','20120401','20','1');
insert into seller2 values (3,'李六','330100198511226666','12345678','1','20120501','10','1');
commit;

create table seller3
(
id number,uname varchar2(30),sfzh varchar2(30),age varchar2(30),create_time varchar2(30),u_level varchar2(30),dept varchar2(30),product_id varchar2(30)
);

insert into seller3 values (1,'张三','410100198111228842','32','20130101','2','10','1');
insert into seller3 values (2,'张四','620100198111223533','32','20130201','2','10','1');
insert into seller3 values (3,'张五','510100198111223578','32','20130701','2','10','1');
commit;

create table pd_users
(
id number,uname varchar2(30),sfzh varchar2(30),age varchar2(30),sex varchar2(30),contact_way varchar2(30),dept_name varchar2(30),create_time varchar2(30),product_type varchar2(30)
);

create table pd_dept
(
id number,deptno varchar2(30),dept_name varchar2(30)
);
insert into pd_dept values (1,'10','管理部');
insert into pd_dept values (2,'20','销售部');
commit;

create table pd_product
(
id number,p_name varchar2(30),price varchar2(30),batch_no varchar2(30),product_type varchar2(30)
);
insert into pd_product values (1,'中兴','1600','Z13001289','电话');
insert into pd_product values (2,'AAAA级电缆','600','BG20130032','电缆');
insert into pd_product values (3,'戴尔','5800','JH7CC4684','电脑');
commit;

若seller2.sfzh=seller1.sfzh ，将seller2插入pd_users；若seller3.sfzh=seller1.sfzh，将seller3插入pd_users；
其中pd_users.dept_name字段的值是pd_dept.dept_name；
pd_users.product_type的值是pd_product.product_type,并且是根据seller2或seller3的product_id关联得来
希望的要的结果如下

[conroy2008]

没人吗？这个好难啊，求高人

[积木]

没细看，如果你觉得 Oracle的 Create table xxx as select × 搞不定，那就得另选办法了。

[conroy2008]

INSERT INTO pd_users
  (id, uname, sfzh, age, sex, contact_way, dept_name, create_time, product_type)
VALUES
  (seq_test.nextval,
   (SELECT uname FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT sfzh FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   NULL,
   (SELECT sex FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT contact_way FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT pd_dept.dept_name FROM pd_dept WHERE pd_dept.deptno in (SELECT seller2.dept FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh))),
  (SELECT create_time FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT pd_product.product_type FROM pd_product WHERE pd_product.id in (SELECT product_id FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)))
   )

自己弄了个，where条件好多一样的，怎么弄到一起

[积木]

哈，我去，你比较狠啊。 给你个建议，你可以用 外连接 的 方式 将这些情报从比较小的集合上组件扩大到最大的集合，然后插入到新表中。

[t051506]

楼主 。你结果有问题。建表的时候没有建索引，pd_users的第二条数据的id是怎么加的，第二条数据的dept_name是管理部不该是销售部额。

[conroy2008]

[zhuhl2011]

引用 4 楼 conroy2008 的回复:

INSERT INTO pd_users
  (id, uname, sfzh, age, sex, contact_way, dept_name, create_time, product_type)
VALUES
  (seq_test.nextval,
   (SELECT uname FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT sfzh FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   NULL,
   (SELECT sex FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT contact_way FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT pd_dept.dept_name FROM pd_dept WHERE pd_dept.deptno in (SELECT seller2.dept FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh))),
  (SELECT create_time FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)),
   (SELECT pd_product.product_type FROM pd_product WHERE pd_product.id in (SELECT product_id FROM seller2 WHERE EXISTS (SELECT 1 FROM seller1 WHERE seller2.sfzh = seller1.sfzh)))
   )

自己弄了个，where条件好多一样的，怎么弄到一起

感觉楼主应该用 select seq_test.nextval，seller2.uname ...... from seller1,seller2,seller3 where seller2.sfzh=seller1.sfzh seller3.sfzh=seller1.sfzh into pd_users 个人感觉这个比你直接写个 insert的语句要好