SQLserver使用映射表进行数据相关操作_MySQL

基本需求：

老数据有老数据的顺序编码规则，新数据有新数据的顺序编码规则，但是老数据的编码还是要更新相应的东西，新数据也得实时更新，在新数据中已经用新编码规则对老数据对进行编码，在上报表中既要新增新数据，也要更新老数据与现有数据。

-------------------------------------------------

--Author:Oliver QIN

--Date:2015-11-07

--DESC:使用映射表对老数据进行更新

-------------------------------------------------

--存储老数据的表

--drop table TB_old

CREATE TABLE TB_old(Id INT,IdSeq VARCHAR(20),Name VARCHAR(200),Adress varchar(200),Age int)

--------------------------------------------------------------------

--插入测试数据（2014年的人员信息表）

---目前的编号是以省份来编号的，例如下面插入的测试数据

/*************************老的人力资源管理系统中******************************/

INSERT INTO TB_old SELECT 1,'yunnan01','JACK','云南昆明',45 union all

SELECT 2,'yunnan02','Tom','云南曲靖',82 union all

SELECT 3,'yunnan03','Alice','云南丽江',60 union all

SELECT 4,'yunnan04','Jerry','云南昭通',26 union all

SELECT 5,'chongqin05','Terry','重庆江北',22

---随着时间的变迁，需要把编号进行修改，统一为China开头的编号，例如：China01

--那接着就建立映射表

/*********************************映射表***************************************/

--drop table Mapping

CREATE TABLE Mapping (Old_IdSeq varchar(100),New_IdSeq varchar(100))

--插入映射关系

insert into Mapping select 'yunnan01','China01' union all

select 'yunnan02','China02' union all

select 'yunnan03','China03' union all

select 'yunnan04','China04' union all

select 'chongqin05','China05'

---这是2015年在新人力资源管理系统中新生成的一张信息表 注明：在老系统中已经生成的编号不可以替换成现有编号，需要延用至退休

--drop table TB_New 注：新的人力资源表中JACK的年龄更新为99，并且新加了JOSN的相关信息

CREATE TABLE TB_New(Id INT,IdSeq VARCHAR(20),Name VARCHAR(200),Adress varchar(200),Age int)

--插入新的测试数据

/*************************新的人力资源管理系统中******************************/

INSERT INTO TB_New SELECT 1,'China01','JACK','云南昆明',99 union all

SELECT 2,'China02','Tom','云南曲靖',82 union all

SELECT 3,'China03','Alice','云南丽江',60 union all

SELECT 4,'China04','Jerry','云南昭通',26 union all

SELECT 5,'China05','Terry','重庆江北',22 union all

SELECT 6,'China06','JOSN','广东深圳',25

-------------现在有如下需求

/*********************************************************

随着时间的推移，需要实时的更新他们的相关信息,假设录入该年龄的时间是2014年

2015年系统进行升级，录入的相关信息则按新的规则命名编号，如果年龄大于45岁则不更新相关信息，视为退休

新的系统中已经把老的信息按照新的编码规则进行编码，但是这些信息需要用来上报给总经理查看。

*/

/**************************上报数据库中的信息*******************************************/

/*上报库中的信息是以前老的系统中的数据*/

--drop table Report_Rpt

CREATE TABLE Report_Rpt(Id INT,IdSeq VARCHAR(20),Name VARCHAR(200),Adress varchar(200),Age int)

INSERT INTO Report_Rpt select * from TB_old

/**************************************************************************************

要求：使用新的人力资源管理系统中提供的数据对更新上报信息中的年龄

*/

--建立映射视图，用于关联更新

go

--select * from Differrence_Data

CREATE VIEW Differrence_Data

as

select A.Age,B.Old_IdSeq from TB_New A

left join Mapping B ON A.IdSeq=B.New_IdSeq

left join TB_old C ON C.IdSeq=B.Old_IdSeq

WHERE A.AgeC.Age

---------------------------------------------------------

GO

--DROP PROC Get_NewInfo

CREATE PROC Get_NewInfo

AS

BEGIN

-------------------------------------------------------情况一：更新老编码对应的年龄信息

if((select COUNT(*) from Differrence_Data)>0)

-------------------------如果在视图Differrence_Data中存在数据，那么更新相应的老编码信息

UPDATE Report_Rpt SET Age=A.Age FROM Differrence_Data A WHERE A.Old_IdSeq=Report_Rpt.IdSeq

IF ( (select COUNT(1) from TB_New where IdSeq not in

(SELECT New_IdSeq FROM Mapping

union all

select IdSeq FROM Report_Rpt

)

) >0

)

-------------------------------------------------情况二：将新增的数据插入到上报表中

---插入新增的数据到上报信息表中

insert into Report_Rpt

select * from TB_New A WHERE A.IdSeq NOT IN (SELECT New_IdSeq FROM Mapping

UNION all

select IdSeq FROM Report_Rpt)

------------------------------------------------情况三：更新新编码对应的年龄信息

IF (

(SELECT COUNT(1) FROM TB_New WHERE

IdSeq not in

(select New_IdSeq from Mapping)

AND IdSeq IN

(SELECT IdSeq FROM Report_Rpt)

)>0

)

--------------------更新新增加数据年龄变更的信息

UPDATE Report_Rpt SET Age=A.Age FROM TB_New A WHERE

A.IdSeq not in

(select New_IdSeq from Mapping)

AND A.IdSeq IN

(SELECT IdSeq FROM Report_Rpt)

AND A.IdSeq = Report_Rpt.IdSeq

END

--执行存储过程

EXEC Get_NewInfo

--老人力资源表

select * from TB_old

--新人力资源表

select * from TB_New

--映射表

select * from Mapping

--上报表

select * from Report_Rpt

--------------------------------------------

--删除表语句

--DROP TABLE TB_old,TB_New,Mapping,Report_Rpt

--------------------------------以下是测试老编码年龄变化跟新编码年龄变化还有新增新的人员信息时，是否也会更新跟插入相应的数据

--------------------------------------改变新人力资源系统中的数据进行逻辑测试

insert into TB_New SELECT 7,'China07','测试01','云南昆明',21 union all

SELECT 8,'China08','测试02','云南昆明',33

---------------修改新数据进行测试

update TB_New set Age='100' where Id='6'

---------------修改老数据的年龄进行测试

update TB_New set Age='100' where Id='1'