I need to find duplicate records (ID) which have different dates, however the duplicate has to be on the day before/after the original. Essentially I am trying to find if the same ID's were used on a different day.
我需要找到具有不同日期的重复记录(ID),但副本必须在原始日期之前/之后。基本上我试图找出是否在不同的日子使用相同的ID。
I am able to find the duplicates but cannot get the date part correct, is there a simpler way to perform the above?
我能够找到重复但不能使日期部分正确,是否有更简单的方法来执行上述操作?
I have been trying the following but feel I am over complicating things:
我一直在尝试以下但感觉我过于复杂化了:
SELECT *
FROM table
WHERE ID IN (
SELECT ID
FROM Table
Where [DATE] < DATEADD(day, +1, [DATE]) and ID=ID
GROUP BY ID
HAVING COUNT(*) > 1 )
ORDER BY Name,[DATE], ID ASC
My data is similar to:
我的数据类似于:
Name Date ID
A 3/30/2018 6.26
B 3/31/2018 6.26
C 4/1/2018 7.85
D 4/2/2018 11.88
E 4/3/2018 11.88
F 4/4/2018 9.48
The query should only pick up names AB and DE.
查询应该只选择名称AB和DE。
Any help would be appreciated.
任何帮助,将不胜感激。
3 个解决方案
#1
1
SELECT NAME,
DATE,
ID
FROM TABLE1
WHERE ID IN (SELECT ID
FROM TABLE1
GROUP BY ID
HAVING COUNT(*) > 1)
Output
NAME DATE ID
A 2018-03-30 6.26
B 2018-03-31 6.26
D 2018-04-02 11.88
E 2018-04-03 11.88
Demo
#2
0
You can use exists:
你可以使用exists:
select t.*
from t
where exists (select 1
from t t2
where t2.id = t.id and
t2.date = dateadd(day, -1, t1.date)
);
This selects the later duplicate. For the earlier one, use 1 instead of -1.
这将选择后面的副本。对于较早的一个,使用1而不是-1。
#3
0
You may try the following Method
您可以尝试以下方法
DECLARE @T TABLE
(
Nm VARCHAR(50),
Mydate DATE,
Id FLOAT
)
INSERT INTO @T
VALUES('A','3/30/2018',6.26),
('B','3/31/2018',6.26),
('C','4/1/2018',7.85),
('D','4/2/2018',11.88),
('E','4/3/2018',11.88),
('F','4/4/2018',9.48)
SELECT
*
FROM @T T
WHERE EXISTS
(
SELECT 1 FROM @T WHERE ID = T.Id GROUP BY Id HAVING COUNT(1)>1
)
#1
1
SELECT NAME,
DATE,
ID
FROM TABLE1
WHERE ID IN (SELECT ID
FROM TABLE1
GROUP BY ID
HAVING COUNT(*) > 1)
Output
NAME DATE ID
A 2018-03-30 6.26
B 2018-03-31 6.26
D 2018-04-02 11.88
E 2018-04-03 11.88
Demo
#2
0
You can use exists:
你可以使用exists:
select t.*
from t
where exists (select 1
from t t2
where t2.id = t.id and
t2.date = dateadd(day, -1, t1.date)
);
This selects the later duplicate. For the earlier one, use 1 instead of -1.
这将选择后面的副本。对于较早的一个,使用1而不是-1。
#3
0
You may try the following Method
您可以尝试以下方法
DECLARE @T TABLE
(
Nm VARCHAR(50),
Mydate DATE,
Id FLOAT
)
INSERT INTO @T
VALUES('A','3/30/2018',6.26),
('B','3/31/2018',6.26),
('C','4/1/2018',7.85),
('D','4/2/2018',11.88),
('E','4/3/2018',11.88),
('F','4/4/2018',9.48)
SELECT
*
FROM @T T
WHERE EXISTS
(
SELECT 1 FROM @T WHERE ID = T.Id GROUP BY Id HAVING COUNT(1)>1
)