如何使用不等于加入 [英] How to use not equal to in join

问题描述

这是我的表格的脚本
 
 
创建表格IM 
（
 ID int idenetity（1,1）
，代码varchar（100）
，CurrentMrp float 
，CurrentCP float 
，lastMrp float 
，lastCp float 
，effective_st_dt date 
，effective_end_dt date 
）
 
创建表格IME 
（
代码varchar（100）
，CurrentMrp float 
，CurrentCP float 
，Effective_st_dt date 
，Effective_end_dt date 
）
 
插入IM（code，currentMrp，currentCp，lastMRP，lastCP，effective_st_dt，effective_end_dt）
选择'CA123'，10.12， 5.0,8.20,4，'2014-05-01'，'2014-05-31'
 union 
选择'CA123'，15.0,5.0,10.12,8.20，'2014-06-01' ，'2014-08-31'
 union 
选择'CA121'，50.0,15.0,45.0,25.0，'2014-04-01'，'2014-05-31'
 union 
选择'CA121'，75.0,25.0,50.0,15.0，'2014-06-01'，'2014-06-30'
 union 
选择'CA131'，53.0,12.0， 35.0,10.0， '2014-05-01'， 2014-05-31'
 union 
选择'CA131'，60.0,15.0,53.0,12.0，'2014-06-01'，'2014-08-31'
 
 
插入IME（代码，effective_st_dt，effective_end_dt）
选择（'CA123'，20.0,5.0，'2014-06-01'，'2014-08-31'）
 union 
选择（'CA123'，25.0,6.0，'2014-06-20'，'2014-08-31'）
 union 
选择（'CA123'，35.0,7.0，' 2014-07-15'，'2015-03-31'）
 
 
我有三种方案可以实施。 
 
案例1-当IM.Code = IME.Code和IM.Effective_st_dt = IME.effective_st_dt和IM.Effective_end_dt = IME.Effective-end_date 
 
行动 -  
在这种情况下，我需要使用currentMrp和currentCp更新IM。 
 
案例2-当IM.Code = IME.Code和IM.Effective_st_dt！= IME.effective_st_dt和IM.Effective_end_dt = IME.Effective-end_date 
 
行动 -  
 1.）从IM中选择最近的Effective_st_dt记录并使用（IME.Effective_st_dt）更新Effective_end_date -1 
 
 2.）然后从IME插入一条新记录，该记录还有新的effective_st_dt此记录lastMRP和lastcp是前一条记录的currentmrp和Currentcp，更新为1 
 
情况3-当IM.Code = IME.Code和IM.Effective_st_dt！= IME.effective_st_dt和IM.Effective_end_dt！= IME.Effective-end_date 
 
行动 -  
 1.）从IM中选择最近的Effective_st_dt记录，并使用（IME.Effective_st_dt）更新Effective_end_date -1 
 
 2 。）然后从IME插入一条新记录，其中有新的effective_st_dt，此记录也是lastMRP，lastcp是当前的mm和前一记录的Currentcp，更新为1 

解决方案

This is the script of my tables


Create Table IM 
	(
		ID int idenetity (1,1)
		,Code varchar(100)
		,CurrentMrp float
		,CurrentCP float
		,lastMrp float
		,lastCp float
		, effective_st_dt date
		,effective_end_dt date
	)

Create table IME
	(
		Code varchar(100)
		,CurrentMrp float
		,CurrentCP float
		,Effective_st_dt date
		,Effective_end_dt date
	)

insert into IM (code,currentMrp,currentCp,lastMRP,lastCP,effective_st_dt,effective_end_dt)
	Select 'CA123',10.12,5.0,8.20,4,'2014-05-01','2014-05-31'
union
	Select 'CA123',15.0,5.0,10.12,8.20,'2014-06-01','2014-08-31'
union
	Select 'CA121',50.0,15.0,45.0,25.0,'2014-04-01','2014-05-31'
union
	Select 'CA121',75.0,25.0,50.0,15.0,'2014-06-01','2014-06-30'
union
	Select 'CA131',53.0,12.0,35.0,10.0,'2014-05-01','2014-05-31'
union
	Select 'CA131',60.0,15.0,53.0,12.0,'2014-06-01','2014-08-31'


Insert into IME (code,effective_st_dt,effective_end_dt)
	Select ('CA123',20.0,5.0,'2014-06-01','2014-08-31')
union
	Select ('CA123',25.0,6.0,'2014-06-20','2014-08-31')
union
	Select ('CA123',35.0,7.0,'2014-07-15','2015-03-31')
	

I have 3 scenarios to implment.

Case 1- When IM.Code=IME.Code and IM.Effective_st_dt=IME.effective_st_dt and IM.Effective_end_dt =IME.Effective-end_date

Action- 
In this case i need to update the IM with currentMrp and currentCp.

Case 2- When IM.Code=IME.Code and IM.Effective_st_dt!=IME.effective_st_dt and IM.Effective_end_dt =IME.Effective-end_date

Action- 
1.)Pick the recent Effective_st_dt record from IM and Update the Effective_end_date with (IME.Effective_st_dt)-1

2.)then Insert a new record from IME which has new effective_st_dt also this record lastMRP and lastcp is the currentmrp and Currentcp of the previous record which is updated in 1

Case 3- When IM.Code=IME.Code and IM.Effective_st_dt!=IME.effective_st_dt and IM.Effective_end_dt !=IME.Effective-end_date

Action-
1.)Pick the recent Effective_st_dt record from IM and Update the Effective_end_date with (IME.Effective_st_dt)-1

2.)then Insert a new record from IME which has new effective_st_dt also this record lastMRP and lastcp is the currentmrp and Currentcp of the previous record which is updated in 1

解决方案

这篇关于如何使用不等于加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！