SQL查找多个字段不匹配

您可以使用一个使用所有字段的联接,并计算其中有多少字段匹配,只保留四个字段中正好有三个匹配的记录:

select     t1.*, 
           t2.*
from       t1 
inner join t2
        on (case when t1.name    = t2.name    then 1 else 0 end)
         + (case when t1.surname = t2.surname then 1 else 0 end)
         + (case when t1.id      = t2.id      then 1 else 0 end)
         + (case when t1.status  = t2.status  then 1 else 0 end) = 3

此查询可能无法从索引中获益,因此您应该验证它对数据的执行方式。

您可以使用 join :

select t1.*, t2.*
from t1 join
     t2
     on (t1.name = t2.name and t1.surname = t2.surname) or
        t1.id = t2.id 
where t1.id <> t2.id or t1.status <> t2.status or
      t1.name <> t2.name or t1.surname <> t2.name;

将列别名与同义词一起使用

SELECT T1.ID AS [T1.ID]
    ,T1.NAME AS [T1.Name]
    ,T1.Surname AS [T1.Surname]
    ,T1.STATUS AS [T1.Status]
    ,T2.ID AS [T2.ID]
    ,T2.NAME AS [T2.Name]
    ,T2.Surname AS [T2.Surname] 
    ,T2.STATUS AS  [T2.Status]
FROM T1
INNER JOIN T2 ON T1.NAME = T2.NAME
    AND T1.Surname = T2.Surname
    AND T1.ID <> T2.ID
    AND T1.STATUS <> T2.STATUS