比较sql server中同一张表的每条记录并返回重复项

2022-01-16 00:00:00 sql-server-2008 sql-server sql-server-2008-r2

我有如下表.我想获取重复的记录.这里的条件是如果 date2 和 date4 具有相同的日期或日期在彼此之间小于或等于 10 天，则记录是重复的.我在 DB 中有大约 2000 条记录.在这里展示几个示例.Date1 可以忽略.日期可能相同，也可能不同.

I have table like below.I wanted to get the duplicate records.Here the condition if date2 and date4 having same date OR dates within less than or equals to 10 days of each other then records are duplicate. I have around 2000 records in the DB.Showing here few sample example. Date1 can be ignored. It may be same date or may be different.

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

另外，我把这个绑起来了:

Also, I tied this:

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

问题是我无法找到一种方法，可以将每一行与上述日期条件进行比较.我应该得到如下所示的重复结果:

The problem is I'm not able to find a way, where each row can be compared with each other with above date condition.I should get the result like below as duplicate:

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

请帮忙.谢谢.

推荐答案

我就是这样解决这个问题的.感谢您的帮助.

This is how I solved this. Thanks for help.

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

相关文章