关于SQL两表关联的Insert有关问题

关于SQL两表关联的Insert问题?

SQL code



条件：
select distinct EcrNo,SendData as ECORELEASEDATE from RES_ECO_PASS where EcrNo in (select ECRNO from RES_ECR_TYPE)

EcrNo                                              ECORELEASEDATE
-------------------------------------------------- -----------------------
ECR-019625                                         2008-12-12 00:00:00
ECR-019631                                         2008-12-12 00:00:00
ECR-019638                                         2008-12-12 00:00:00
ECR-019652                                         2008-12-12 00:00:00
ECR-019653                                         2008-12-12 00:00:00
ECR-019655                                         2008-12-12 00:00:00
ECR-019656                                         2008-12-12 00:00:00
ECR-019662                                         2008-12-12 00:00:00
ECR-019663                                         2008-12-12 00:00:00
ECR-019664                                         2008-12-12 00:00:00
ECR-019667                                         2008-12-12 00:00:00
ECR-019668                                         2008-12-12 00:00:00
ECR-019669                                         2008-12-12 00:00:00
ECR-019672                                         2008-12-12 00:00:00
ECR-019678                                         2008-12-12 00:00:00
ECR-019679                                         2008-12-12 00:00:00
ECR-019682                                         2008-12-12 00:00:00
ECR-019683                                         2008-12-12 00:00:00
ECR-019684                                         2008-12-12 00:00:00
ECR-019686                                         2008-12-12 00:00:00

(20 個資料列受到影響)

关联插入以上20笔记录：
insert into RES_ECR_TYPE(ECONO,ECORELEASEDATE) select distinct a.EcrNo,a.SendData as ECORELEASEDATE from RES_ECO_PASS a left join RES_ECR_TYPE b on 
a.EcrNo=b.ECONO and a.SendData=b.ECORELEASEDATE where a.EcrNo in (select b.ECRNO from RES_ECR_TYPE)

为何Insert为0笔记录，显示：(0 個資料列受到影響),是不是SQL有问题？？？请大侠指点！！！

------解决方案--------------------
楼主的第二条语句中多了一个筛选条件a.SendData=b.ECORELEASEDATE，导致符合筛选条件的结果行数为0.
其实那个join语句是完全没必要的，第一个筛选条件on a.EcrNo=b.ECONO也与后面的where子句作用重复。

建议回归为第一个句子的形式：

SQL code


insert into RES_ECR_TYPE(ECONO,ECORELEASEDATE)
select distinct EcrNo,SendData as ECORELEASEDATE from RES_ECO_PASS where EcrNo in (select ECRNO from RES_ECR_TYPE)

------解决方案--------------------

SQL code


    update a set a.ECONO=b.ECONO
    from RES_ECR_TYPE a,
    (
    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE 
    from RES_ECO_PASS 
    where EcrNo in (select ECRNO from RES_ECR_TYPE)
    )b
    where a.ECRNO=b.EcrNo
    
    --update<ECOCC>
    update a set a.ECROCC=b.ECOCC
    from RES_ECR_TYPE a,
    (
    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE 
    from RES_ECO_PASS 
    where EcrNo in (select ECRNO from RES_ECR_TYPE)
    )b 
    where a.ECRNO=b.EcrNo
    
    --update<ECORELEASEDATE>
    update a set a.ECORELEASEDATE=b.ECORELEASEDATE
    from RES_ECR_TYPE a,
    (
    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE
    from RES_ECO_PASS
    where EcrNo in (select ECRNO from RES_ECR_TYPE)
    )b
    where a.ECRNO=b.EcrNo

关于SQL两表关联的Insert有关问题

相关推荐