求两个表的数据比较后插入另外一个表SQL

[haoztao]

目前两个表结构为：

create table individualcheckTaskData 
(

    branchid         varchar2(20),
    checkerno           varchar2(20),
    checkername           varchar2(20),
    siglecheckEfficiency    float,
    checktotalnum      varchar2(20)
);
create table individualendorTaskData 
(

    branchid         varchar2(20),
    endendorno           varchar2(20),
    endendorname           varchar2(20),
    sigleendorEfficiency    float,
    endortotalnum      varchar2(20)
);
--要插入数据的表的结构为：
create table individualBmdmTaskData 
(
    examinedUser         varchar2(20)                   not null,
    examinedName         varchar2(20),
    belongBmdm           varchar2(20)                   not null,
    checkNum             float,
    endorNum             float,
    checkEfficiency      varchar2(20),
    endorEfficiency      varchar2(20)
    constraint PK_INDIVIDUALBMDMTASKDATA primary key (examinedUser, belongBmdm)
);

说明：individualcheckTaskData 和individualendorTaskData 表中的branchid是一致的，但是checkerno和endendorno可能不一致，插入规则是：
1.如果两个表branchid一样，checkerno和endendorno一样，则插入individualBmdmTaskData 表其中examinedUser和examinedName为上述两表中的checkerno，checkername；其中belongBmdm为branchid，checkNum为checktotalnum，endorNum为endortotalnum，checkEfficiency为siglecheckEfficiency，endorEfficiency为sigleendorEfficiency。
2.如果individualcheckTaskData 中checkerno有值而individualendorTaskData 中没有相关信息，则写入individualcheckTaskData表中信息，其他值为0
3.如果individualendorTaskData 中endendorno有值而individualcheckTaskData 中没有相关信息，则写入individualendorTaskData 表中信息，其他值为0
急求。。SQL语句

[阿健来了]

楼主的
checkerno和endendorno一样
和
如果individualcheckTaskData 中checkerno有值而individualendorTaskData 中没有相关信息
是不是矛盾啊

[oO寒枫Oo]

1.

merge into individualBmdmTaskData iv using
(
 select ch.branchid,ch.checkerno,ch.checkername,ch.siglecheckEfficiency,ch.checktotalnum,en.endendorname,en.sigleendorEfficiency,en.endortotalnum
 from individualcheckTaskData ch,individualendorTaskData en
 where ch.branchid=en.branchid and ch.checkerno=en.endendorno
) ce on (iv.examinedUser=ce.checkerno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency)
values(ce.checkerno,ce.checkername,ce.branchid,ce.checktotalnum,ce.endendorname,ce.sigleendorEfficiency,ce.endortotalnum);
commit;

[oO寒枫Oo]

2.

merge into individualBmdmTaskData iv using
(
 select ch.branchid,ch.checkerno,ch.checkername,ch.siglecheckEfficiency,ch.checktotalnum
 from (select * from individualcheckTaskData where checkerno is not null) ch left join (select * from individualendorTaskData where checkerno is null) en on ch.branchid=en.branchid
 where 1=1
) ce on (iv.examinedUser=ce.checkerno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency)
values(ce.checkerno,ce.checkername,ce.branchid,ce.checktotalnum,0.0,null,null);
commit;

[oO寒枫Oo]

2.

merge into individualBmdmTaskData iv using
(
 select ch.branchid,ch.checkerno,ch.checkername,ch.siglecheckEfficiency,ch.checktotalnum
 from (select * from individualcheckTaskData where checkerno is not null) ch left join (select * from individualendorTaskData where endendorno is null) en on ch.branchid=en.branchid
 where 1=1
) ce on (iv.examinedUser=ce.checkerno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency)
values(ce.checkerno,ce.checkername,ce.branchid,ce.checktotalnum,0.0,null,null);
commit;

3.
merge into individualBmdmTaskData iv using
(
 select en.branchid,en.endendorno,en.endendorname,en.sigleendorEfficiency,en.endortotalnum
 from  (select * from individualendorTaskData where endendorno is not null) en left join  (select * from individualcheckTaskData where checkerno is null) ch on ch.branchid=en.branchid
 where 1=1
) ce on (iv.examinedUser=ce.endendorno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency)
values(ce.endendorno,ce.endendorname,ce.branchid,0.0,ce.endortotalnum,null,ce.sigleendorEfficiency);
commit;

[我心飞翔]

对于第1种情况实测成功：

merge into individualBmdmTaskData iv using
(
 select ch.branchid,ch.checkerno,ch.checkername,ch.siglecheckEfficiency,ch.checktotalnum,en.endendorname,en.sigleendorEfficiency,en.endortotalnum
 from individualcheckTaskData ch,individualendorTaskData en
 where ch.branchid=en.branchid and ch.checkerno=en.endendorno
) ce on (iv.examinedUser=ce.checkerno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency)
values(ce.checkerno,ce.checkername,ce.branchid,ce.checktotalnum,ce.endendorname,ce.sigleendorEfficiency,ce.endortotalnum);
commit;

[我心飞翔]

对于第2种和第3种，可以参考第1种进行改进。

[haoztao]

merge into individualBmdmTaskData iv using
(
 select ch.branchid,ch.checkerno,ch.checkername,ch.siglecheckEfficiency,ch.checktotalnum,en.endendorname,en.sigleendorEfficiency,en.endortotalnum
 from individualcheckTaskData ch,individualendorTaskData en
 where ch.branchid=en.branchid and ch.checkerno=en.endendorno
) ce on (iv.examinedUser=ce.checkerno and iv.belongBmdm=ce.branchid)
when not matched then
insert (examinedUser,examinedName,belongBmdm,checkNum,endorNum,checkEfficiency,endorEfficiency,Datacycle)
values(ce.checkerno,ce.checkername,ce.branchid,ce.checktotalnum,ce.endortotalnum,ce.siglecheckEfficiency,ce.sigleendorEfficiency,'2011');
commit;

这个可以 哈哈