显示重复了2次以上,同时是在2天内重复的?
表T
ID Code Name Time
1 001 小张 2007-9-1 18:00
2 002 王平 2007-9-1 18:01
3 001 小张 2007-9-1 19:01
4 003 黑子 2007-9-1 19:20
5 005 大大 2007-9-1 19:21
6 002 王平 2007-9-1 20:11
7 002 王平 2007-9-2 12:00
... ...
显示结果为:
ID Code Name Time
1 001 小张 2007-9-1 18:00
3 001 小张 2007-9-1 19:01
2 002 王平 2007-9-1 18:01
6 002 王平 2007-9-1 20:11
7 002 王平 2007-9-2 12:00
... ...
怎么写sql语句?
表T
ID Code Name Time
1 001 小张 2007-9-1 18:00
2 002 王平 2007-9-1 18:01
3 001 小张 2007-9-1 19:01
4 003 黑子 2007-9-1 19:20
5 005 大大 2007-9-1 19:21
6 002 王平 2007-9-1 20:11
7 002 王平 2007-9-2 12:00
... ...
显示结果为:
ID Code Name Time
1 001 小张 2007-9-1 18:00
3 001 小张 2007-9-1 19:01
2 002 王平 2007-9-1 18:01
6 002 王平 2007-9-1 20:11
7 002 王平 2007-9-2 12:00
... ...
怎么写sql语句?
inner join
(select Code from T group by Code having count(*)>=2) A
on T.Code=A.Code
orde by T.Code
WHERE Code in
(
SELECT Code FROM T
GROUP BY Code
HAVING datediff(minute,max(Time),min(Time))<=2880
)
WHERE Code in
(
SELECT Code FROM T
GROUP BY Code
HAVING datediff(minute,max(Time),min(Time)) <=2880
and count(ID)>2
)
2改为3即可
1 2007-10-1 a
2 2007-10-2 a
3 2007-10-3 a
1,2两条记录是在两天内重复的
2,3两条记录是在两天内重复的
那么在10.1和10.2之间, 在10.2和10.3之间各重复了一次, 这种情况下要不要按重复3次算或者是1 2007-10-1 a
2 2007-10-2 a
3 2007-10-2 a
三次都重复在两天内才算是重复3次.你的取数规则不同,那么语句的写法就有不同.
insert into @T values(1,'001','小张','2007-9-1 18:00')
insert into @T values(2,'002','王平','2007-9-2 18:01')
insert into @T values(3,'001','小张','2007-9-2 19:01')
insert into @T values(4,'003','黑子','2007-9-1 19:20')
insert into @T values(5,'005','大大','2007-9-1 19:21')
insert into @T values(6,'002','王平','2007-9-1 20:11')
insert into @T values(7,'002','王平','2007-9-2 12:00')
insert into @T values(8,'001','小张','2007-9-3 18:00')
select a.*
from @t a
inner join
(
select code,name,gid
from
(select *,isnull(
(select min(id)
from @t b where id<a.id and code=a.code
and abs(datediff(dd,Time,a.Time))<2
)
,id) gid
from @t a
) x
group by code,name,gid
having count(gid)>2
) x
on x.code=a.code and x.name=a.name
and isnull(
(select min(id) from @t b
where id<a.id and code=a.code
and abs(datediff(dd,Time,a.Time))<2
)
,id)
=x.gid
declare @T table(ID int,Code varchar(10),Name varchar(10),Time datetime)
insert into @T values(1,'001','小张','2007-9-1 18:00')
insert into @T values(2,'002','王平','2007-9-1 18:01')
insert into @T values(3,'001','小张','2007-9-1 19:01')
insert into @T values(4,'003','黑子','2007-9-1 19:20')
insert into @T values(5,'005','大大','2007-9-1 19:21')
insert into @T values(6,'002','王平','2007-9-1 20:11')
insert into @T values(7,'002','王平','2007-9-2 12:00')
select * from @t a where code in
(select code from @t where abs(datediff(dd,a.time,time))<2 group by code having count(1)>=2)
order by code显示重复了2次以上,同时是在2天内重复的?
---------------------------------
前面的<2代表2天内重复的,后面的>=2,代表重复2次以上。楼主可以根据不同需求修改这两个。