比较sql server中相同表的每个记录并返回重复记录。

Question

我有如下表，我想要得到重复的records.Here条件，如果date2和date4有相同的日期或日期，在彼此不到或等于10天之内，那么记录是重复的。我在这里的DB.Showing中有大约2000条记录，很少有示例例子。Date1是可以忽略的。可能是同一日期，也可能是不同的日期。

ID Number   Code Type   date1     Date2       date3        Date4     status   shortname     CP      deferred
1  EO2      C    TO   9/20/2000  9/1/2010   9/18/2010   9/1/2010     Archi    Ordinary  58.65586    0
2  EO2      C    TO   9/20/2000  9/5/2010   9/18/2010   9/5/2010     Archi    Ordinary  58.65586    0
3  EO2      C    TO   9/21/2000  9/10/2010  9/18/2010   9/10/2010    Archi    Ordinary  58.65586    0
4  EO2      C    TO   9/21/2000  9/24/2010  9/18/2010   9/24/2010    Archi    Ordinary  58.65586    0

我写了以下查询：

select * from T a
join T b on a.ID = b.ID
where a.[Number] = b.[Number] and a.ID >1

还有，我把这个绑了起来：

SELECT * FROM T a WHERE a.Id IN (SELECT b.Id FROM T b)
EXCEPT
SELECT * FROM T a

问题是我找不到一种方法，在上面的日期条件下，每一行都可以进行比较，我应该得到如下的结果：

Number  Code Type   date1     Date2       date3        Date4     status   shortname     CP      deferred
EO2     C    TO   9/20/2000  9/1/2010   9/18/2010   9/1/2010     Archi    Ordinary  58.65586    0
EO2     C    TO   9/20/2000  9/5/2010   9/18/2010   9/5/2010     Archi    Ordinary  58.65586    0
EO2     C    TO   9/21/2000  9/10/2010  9/18/2010   9/10/2010    Archi    Ordinary  58.65586    0

请help.Thanks。

Answer 1

我就是这样解决这个问题的。谢谢你帮忙。

select t1.Number,t1.Code,t1.Type,t1.date1,t1.Date2,t1.date3,t1.Date4,t1.stats,t1.shortn‌​ame,t1.CP, t1.deferred 
from T t1 join T t2 on 1=1 and t2.Number = t1.Number and t2.Code = t1.Code and t2.Type = t1.Type and t2.deferred = t1.eferred and t2.CP = t1.CP and t2.status = t1.status and abs(datediff(day,t1.Date2,t2.Date2)) <=10 and abs(datediff(day,t1.date3,t2.date3)) <=10 and abs(datediff(day,t1.Date4,t2.Date4)) <=10 group by t1.Number, t1.Code,t1.Type , t1.date1, t1.Date2,t1.date3,t1.Date4 ,t1.status,t1.shortname,t1.CP, t1.deferred having count(*) > 1

Answer 2

您可以使用join搜索其他相同的行。join试图根据on条件匹配右手表中的所有行。例如：

select  *
from    YourTable t1
join    YourTable t2
on      t1.ID < t2.ID -- Must be different rows
                      -- Smaller than presents the duplicates once
                      -- Otherwise you'd get both 1,3 and 3,1
        and abs(datediff(day, t1.date2, t2.date2)) <= 10
        and abs(datediff(day, t1.date4, t2.date4)) <= 10
        and t1.Number = t2.Number
        and t1.Code = t2.Code
        and -- So on for every column that should be equal

若要忽略列，请从on条件中省略该列。