需要SQL语句专注于表的组合,但条目始终具有唯一ID

前端之家收集整理的这篇文章主要介绍了需要SQL语句专注于表的组合,但条目始终具有唯一ID前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我需要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
原文链接:https://www.f2er.com/mysql/432952.html

猜你在找的MySQL相关文章