关于SQL两表关联的Insert有关问题
日期：2014-05-17 浏览次数：20671 次
关于SQL两表关联的Insert问题?
------解决方案--------------------
楼主的第二条语句中多了一个筛选条件a.SendData=b.ECORELEASEDATE， 导致符合筛选条件的结果行数为0.
其实那个join语句是完全没必要的，第一个筛选条件on a.EcrNo=b.ECONO也与后面的where子句作用重复。
建议回归为第一个句子的形式：
- 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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
