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

2011-12-13 02:40:46 273 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

保姆级教程：用ROS2 Humble和Nav2从零搭建一个能自主导航的机器人小车（含SLAM建图与避障）本文提供了一份详细的保姆级教程，指导如何使用ROS2 Humble和Nav2从零搭建一个具备自主导航功能的机器人小车，包括SLAM建图与避障。教程涵盖了硬件组装、ROS2环境配置、URDF模型创建、SLAM建图实战、Nav2导航栈配置以及系统集成调试等关键步骤，适合机器人开发初学者和进阶开发者。阅读详情

目前两个表结构为：



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语句

...全文

CODESYS V3.5 SP17编程环境全攻略：从安装到多语言编程实战本文全面解析CODESYS V3.5 SP17编程环境，从安装配置到多语言编程实战技巧。详细介绍了运行时环境设置、六种IEC 61131-3语言的特性对比与混合编程策略，以及高级功能开发如自定义算法模块和实时通信配置。适合工业自动化工程师快速掌握这一强大工具。阅读详情

haoztao

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

haoztao 2011-12-14

打赏
举报



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;

这个可以哈哈

我心飞翔 2011-12-13

打赏
举报

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

我心飞翔 2011-12-13

打赏
举报

对于第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 2011-12-13

打赏
举报



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;

oO寒枫Oo 2011-12-13

打赏
举报



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 2011-12-13

打赏
举报





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;

阿健来了 2011-12-13

打赏
举报

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

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

相关推荐