如何从SQL Server中的两个表返回唯一行

I have two student tables in SQL Server, a base table from my original run and the new table from the next days run. Each table has info like Building, UserID LastName FirstName:

我在SQL Server中有两个学生表,一个来自原始运行的基表和来自下一天运行的新表。每个表都有像Building,UserID LastName FirstName这样的信息:

Base table:

3   2509381 *s  Corey   
3   2527352 Doss    Dawone  
14  170163  Belin   Teaira  
14  2465666 Arlington   Xavier  
14  2465941 Smith   Jerald  
14  2466022 Junious Dontrell    
14  2466898 Shelton Rayonna 
14  2468144 Sullivan    James   
14  2468612 Brown   Jerron  
14  2469949 Quinn   Jordan

New table:

3   2527352 Doss    Dawone  
14  170163  Belin   Teaira  
14  2465666 Arlington   Xavier  
14  2465941 Smith   Jerald  
14  2466022 Junious Dontrell    
14  2466898 Shelton Rayonna 
14  2468144 Sullivan    James   
14  2468612 Brown   Jerron  
3   2469949 Quinn   Jordan  
14  1234512 Davis   John

In my example here, *s was deleted, Quinn had building number changed, and Davis was added I'd like my result table to look something like this.

在我的例子中,*s被删除了,Quinn修改了建筑物编号,并添加了戴维斯,我希望我的结果表看起来像这样。

Audit table:

3   2509381 *s  Corey   
14  2469949 Quinn   Jordan  
14  1234512 Davis   John

I am using two except queries, to get these results

我使用两个除了查询,以获得这些结果

select * from test1 except select * from test2

select * from test1除了select * from test2

3   2509381 *s  Corey   
14  2469949 Quinn   Jordan

select * from test2 except select * from test1

select * from test2除了select * from test1

3   2469949 Quinn   Jordan  
14  1234512 Davis   John

How do I get them to output all the deltas in the audit table. I tried to Union my two select statements but that didn't work...

如何让它们输出审计表中的所有增量。我尝试联合我的两个选择语句,但这不起作用......

2 个解决方案

#1

I am assuming that the student numbers are unique? Or there is some way to determine what is a unique row if you haven't provided all columns. You need this, otherwise you cannot detect what is new and what is changed. Or what is deleted and added.

我假设学生人数是独一无二的?或者如果您没有提供所有列,还有一些方法可以确定什么是唯一行。你需要这个,否则你无法检测到什么是新的和什么是改变的。或者删除和添加的内容。

Typically we would use a query that joins items together on a unique field, which could be UserID in this case. Your join would then need to compare the various other fields.

通常,我们会使用在唯一字段上将项目连接在一起的查询,在这种情况下可能是UserID。然后,您的联接需要比较其他各个字段。

An outer join will tell if you something about inserts and deletes. For example, I could do this:

外连接将告诉您是否有关于插入和删除的信息。例如,我可以这样做:

select *
 from base b
   full outer join new n
   on b.userid = n.userid

If you do this, then you'll see various things. If there are NULLs for the b.userid but not n.userid, then you have new rows in the New table that don't exist in the base table. If you have the reverse, then you have had a delete. For changes, you could compare values of each column and then determine what's different. You might choose to handle this in a few queries that each look for separate things, like an insert query, a delete query, and a change query. You can UNION those together for a result hat you can insert into an audit table.

如果你这样做,那么你会看到各种各样的东西。如果b.userid但不是n.userid存在NULL,则New表中的新行在基表中不存在。如果您反过来,那么您已经删除了。对于更改,您可以比较每列的值,然后确定不同的值。您可以选择在几个查询中处理此问题,每个查询都会查找单独的内容,例如插入查询,删除查询和更改查询。您可以将这些联合起来以获得可以插入审计表的结果帽。

Note: In many systems, we would use a trigger to capture these changes in real time for auditing (or another auditing mechanism in SQL Server).

注意:在许多系统中,我们将使用触发器实时捕获这些更改以进行审计(或SQL Server中的其他审计机制)。

#2

One way is to build an exception query, and use an insert into a new table to capture the data:

一种方法是构建异常查询,并使用插入到新表中来捕获数据:

Create table mytable_audit (Building, UserID, Lastname, Firstname, Audit_Date)

创建表mytable_audit(Building,UserID,Lastname,Firstname,Audit_Date)

Insert into mytable_audit
Select Building, UserID, Lastname, Firstname, getdate() from mytable2
EXCEPT 
Select Building, UserID, Lastname, Firstname, getdate() from mytable

The only problem with that approach is it won't tell you why the data is in the table. You can get around this by making this a subquery (provided as wayOutwest said that you have a unique key) and then join back on to the source table. That way you can add a case statement to provide an indicator. Finally a UNION to a second query checks for deleted students:

该方法的唯一问题是它不会告诉您数据在表中的原因。你可以通过将它作为一个子查询来解决这个问题(如wayOutwest所说,你有一个唯一的密钥),然后重新加入源表。这样您就可以添加一个case语句来提供指标。最后,第二个查询的UNION检查已删除的学生:

Create table mytable_audit
    (Building int, Username int, Lastname varchar(100), Firstname varchar(100), Audit_Date datetime, ChangeType varchar(100))

    Insert into mytable_audit

    Select aa.*, getdate(),
    case when aa.building<>mt.building then 'Building Changed'
         when mt.userid is null then 'Student Added'
         when aa.lastname<>mt.lastname or aa.firstname<>mt.firstname then 'Student Name Changed'
    end as changetype
    from 
    (Select Building, UserID, Lastname, Firstname from mytable2
    EXCEPT 
    Select Building, UserID, Lastname, Firstname from mytable) aa

    left join mytable mt on aa.userID=mt.userID

    UNION 

    Select Building, UserID, Lastname, Firstname, getdate(), 'Student Removed' from mytable mt
    left join mytable2 mt2 on mt.userid=mt2.userid
    where mt2.userid is null

#1