将主表与其历史表进行比较 - sql 2008 [英] compare main table to its history table - sql 2008

问题描述

您好，¥b $ b有tblMain和tblMainHistory

tblMain每个ID列有一条记录

tblMainHistory每个MainID列可能有几条记录

如您所见，tblMainHistory是tblMain更改的历史记录。



它们都具有相同的列，并且在历史表中它可能具有如果更新了tblMain中的一列，则每个MainID列有几行。



问题

如何找出更改的列和上一个和当前值？

当前值显然在tblMain中。 tblMainHistory显示之前的情况和当前的结果





谢谢你

Hello,
There is tblMain and tblMainHistory
tblMain has one record per ID column
tblMainHistory may have several records per MainID column
As you see, tblMainHistory is the history of changes to tblMain.

They both have the same columns and in the history table it may have several rows per MainID column if one of the columns in tblMain was updated.

Question
How can I find out which column was changed and the previous and current value?
The current value is obviously in tblMain. tblMainHistory shows what it was before and current


Thank you

推荐答案

以下是可以使用的示例。如果NewValue和OldValue相同，则显示为NULL。

Here is the sample that can be used. If the NewValue and OldValue is same, it shows as NULL.

create table tblMain (MainId int,Column1 varchar(10),Column2 varchar(10));
create table tblMainHistory (MainId int,Column1 varchar(10),Column2 varchar(10));
insert into tblMain values (1,'A2','B1'),(2,'A1','B3');
insert into tblMainHistory values (1,'A1','B1'),(2,'A1','B1'),(2,'A1','B2');

select m.MainId
    ,case when NULLIF(m.Column1,h.Column1) is not null then m.Column1 end New_Column1
    ,case when NULLIF(m.Column1,h.Column1) is not null then h.Column1 end Old_Column1
    ,case when NULLIF(m.Column2,h.Column2) is not null then m.Column2 end New_Column2
    ,case when NULLIF(m.Column2,h.Column2) is not null then h.Column2 end Old_Column2
from tblMain m
inner join tblMainHistory h
    on m.MainId = h.MainId;

drop table tblMain;
drop table tblMainHistory;

示例输出：

MainId      New_Column1 Old_Column1 New_Column2 Old_Column2
----------- ----------- ----------- ----------- -----------
1           A2          A1          NULL        NULL
2           NULL        NULL        B3          B1
2           NULL        NULL        B3          B2

这篇关于将主表与其历史表进行比较 - sql 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！