|
我需要SQL代码来解决表组合问题,如下所述：
表旧数据：表旧
name version status lastupdate ID
A 0.1 on 6/8/2010 1
B 0.1 on 6/8/2010 2
C 0.1 on 6/8/2010 3
D 0.1 on 6/8/2010 4
E 0.1 on 6/8/2010 5
F 0.1 on 6/8/2010 6
G 0.1 on 6/8/2010 7
表新数据：表新
name version status lastupdate ID
A 0.1 on 6/18/2010
#B entry deleted
C 0.3 on 6/18/2010 #version_updated
C1 0.1 on 6/18/2010 #new_added
D 0.1 on 6/18/2010
E 0.1 off 6/18/2010 #status_updated
F 0.1 on 6/18/2010
G 0.1 on 6/18/2010
H 0.1 on 6/18/2010 #new_added
H1 0.1 on 6/18/2010 #new_added
新数据和旧日期的区别：
B条目已删除
C条目版本已更新
E条目状态已更新
C1 / H / H1条目新添加
我想要的是始终保持旧数据表中的ID – 名称映射关系,无论以后数据如何变化,a.k.a名称始终具有与之绑定的唯一ID号.
如果条目已更新,则更新数据,如果条目是新添加的,则插入表格,然后提供新分配的唯一ID.如果该条目已删除,请删除该条目,稍后不要重复使用该ID.
但是,我只能使用带有简单选择或更新语句的SQL,那么我可能很难编写这样的代码,那么我希望有专业知识的人可以给出方向,不需要有关SQL变体,标准sql代码的详细信息.样品就够了.
提前致谢！
RGS
KC
========
我在这里列出了我的草稿sql,但不确定它是否有效,一些有专业知识的人请评论,谢谢！
1.将旧表作为tmp用于商店更新
创建表tmp为
选择*从旧
2.更新到tmp,其中“name”在旧表和新表中相同
更新tmp
其中的名称(从新选择名称)
3.将不同的“名称”(旧的与新的)插入tmp并分配新的ID
插入到tmp(名称版本状态lastupdate ID)
set idvar = max(从tmp中选择max(id))1
选择*来自
??(选择new.name new.version new.status new.lastupdate new.ID
??从旧的,新的
??其中old.name<>新名字)
4.从tmp表中删除已删除的条目(例如B)
从tmp删除
哪里
(选择 ？？？)
最佳答案
您从未提及您正在使用的DBMS,但如果您使用的是SQL Server,那么一个非常好的是SQL MERGE语句.见：http://www.mssqltips.com/tip.asp?tip=1704
The MERGE statement basically works as
separate insert,update,and delete
statements all within the same
statement. You specify a “Source”
record set and a “Target” table,and
the join between the two. You then
specify the type of data modification
that is to occur when the records
between the two data are matched or
are not matched. MERGE is very useful,
especially when it comes to loading
data warehouse tables,which can be
very large and require specific
actions to be taken when rows are or
are not present.
例：
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched,update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,TARGET.Rate = SOURCE.Rate
--When no records are matched,insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID,ProductName,Rate)
VALUES (SOURCE.ProductID,SOURCE.ProductName,SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT','UPDATE',or 'DELETE',--according to the action that was performed on that row
OUTPUT $action,DELETED.ProductID AS TargetProductID,DELETED.ProductName AS TargetProductName,DELETED.Rate AS TargetRate,INSERTED.ProductID AS SourceProductID,INSERTED.ProductName AS SourceProductName,INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
