开始时间 结束时间不固定 ，如何查询

Question

我有这样一批数据，要找到同一ID在半年内出现3次三次以上的数据，开始时间和结束时间不固定，如何写这个条件呢？ID          时间
1000103 2008-06-24 00:00:00.000
1000103 2008-08-11 00:00:00.000
1000103 2008-08-18 00:00:00.000
1000589 2008-12-18 00:00:00.000
1000589 2009-01-20 00:00:00.000
1000589 2009-03-02 00:00:00.000
1001100 2008-08-05 00:00:00.000
1001100 2008-08-19 00:00:00.000
1001100 2008-09-09 00:00:00.000
1001100 2008-09-23 00:00:00.000
1001100 2008-09-24 00:00:00.000
1001100 2008-10-08 00:00:00.000
1001649 2010-07-15 00:00:00.000
1001649 2010-08-30 00:00:00.000
1001649 2010-10-25 00:00:00.000
1001674 2010-05-24 00:00:00.000
1001674 2010-06-11 00:00:00.000
1001674 2010-06-23 00:00:00.000
1001967 2008-09-01 00:00:00.000
1001967 2008-09-26 00:00:00.000
1001967 2008-10-09 00:00:00.000
1002505 2008-12-21 00:00:00.000
1002505 2009-02-23 00:00:00.000
1002505 2009-05-27 00:00:00.000
1002756 2009-08-25 00:00:00.000
1002756 2009-10-30 00:00:00.000
1002756 2009-11-04 00:00:00.000
1002925 2009-10-24 00:00:00.000
1002925 2009-11-04 00:00:00.000
1002925 2010-01-24 00:00:00.000
1002925 2010-03-05 00:00:00.000
1002927 2009-11-09 00:00:00.000
1002927 2009-11-19 00:00:00.000
1002927 2009-11-19 00:00:00.000
1002986 2010-07-07 00:00:00.000
1002986 2010-07-27 00:00:00.000
1002986 2010-08-11 00:00:00.000
1003013 2010-06-01 00:00:00.000
1003013 2010-06-29 00:00:00.000
1003013 2010-07-19 00:00:00.000
1003025 2010-04-23 00:00:00.000
1003025 2010-05-08 00:00:00.000
1003025 2010-05-11 00:00:00.000
1003070 2009-09-01 00:00:00.000
1003070 2009-10-21 00:00:00.000
1003070 2009-12-03 00:00:00.000
1003104 2008-06-25 00:00:00.000
1003104 2008-07-16 00:00:00.000
1003104 2008-08-04 00:00:00.000
1003152 2009-10-24 00:00:00.000
1003152 2009-11-24 00:00:00.000

Answer 1

IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
   ID VARCHAR(10),
   时间 DATETIME
)
INSERT INTO TB
SELECT '1000103','2008-06-24 00:00:00.000' UNION ALL
SELECT '1000103','2008-08-11 00:00:00.000' UNION ALL
SELECT '1000103','2008-08-18 00:00:00.000' UNION ALL
SELECT '1000589','2008-12-18 00:00:00.000' UNION ALL
SELECT '1000589','2009-01-20 00:00:00.000' UNION ALL
SELECT '1000589','2009-03-02 00:00:00.000' UNION ALL
SELECT '1001100','2008-08-05 00:00:00.000' UNION ALL
SELECT '1001100','2008-08-19 00:00:00.000' UNION ALL
SELECT '1001100','2008-09-09 00:00:00.000' UNION ALL
SELECT '1001100','2008-09-23 00:00:00.000' UNION ALL
SELECT '1001100','2008-09-24 00:00:00.000' UNION ALL
SELECT '1001100','2009-09-08 00:00:00.000' UNION ALL
SELECT '1001100','2009-10-08 00:00:00.000' UNION ALL
SELECT '1001649','2010-07-15 00:00:00.000' UNION ALL
SELECT '1001649','2010-08-30 00:00:00.000' UNION ALL
SELECT '1001649','2010-10-25 00:00:00.000' UNION ALL
SELECT '1001674','2010-05-24 00:00:00.000' UNION ALL
SELECT '1001674','2010-06-11 00:00:00.000' UNION ALL
SELECT '1001674','2010-06-23 00:00:00.000' UNION ALL
SELECT '1001967','2008-09-01 00:00:00.000' UNION ALL
SELECT '1001967','2008-09-26 00:00:00.000' UNION ALL
SELECT '1001967','2008-10-09 00:00:00.000' UNION ALL
SELECT '1002505','2008-12-21 00:00:00.000' UNION ALL
SELECT '1002505','2009-02-23 00:00:00.000' UNION ALL
SELECT '1002505','2009-05-27 00:00:00.000' UNION ALL
SELECT '1002756','2009-08-25 00:00:00.000' ;WITH CTE1 AS
(
   SELECT COUNT(*)OVER(PARTITION BY ID) AS COUNT,ID,时间 FROM TB
)
,CTE2 AS
(
SELECT C1.ID,C1.时间,COUNT(*) AS COUNT 
FROM CTE1 C1 JOIN CTE1 C2 ON DATEDIFF(DD,C1.时间,C2.时间)<=183 AND C2.时间>=C1.时间 AND C1.COUNT>=3 and C1.ID=C2.ID 
GROUP BY C1.ID,C1.时间
)
SELECT ID,MAX(COUNT) FROM CTE2 GROUP BY ID HAVING MAX(COUNT)>=3
---------------------------------------
ID (无列名)
1000103 3
1000589 3
1001100 5
1001649 3
1001674 3
1001967 3
1002505 3

Answer 2

--借用#1演示数据
select distinct a.ID 
from tb a join tb b on a.时间 <= b.时间 and b.时间<= dateadd(d,183,a.时间) and a.ID = b.ID 
group by a.ID,a.时间
having count(1) >= 3
/*
ID
----------
1000103
1000589
1001100
1001649
1001674
1001967
1002505
*/记得结贴哦

Answer 3

我要取半年内出现的数据，不需要distinct，只要排除掉这些没有在半年内出现三次的数据就可以了！

Answer 4

是不是这样：select distinct id,时间 
from 
(
select b.*, cn = count(1) over(partition by a.ID,a.时间)
from tb a join tb b on a.时间 <= b.时间 and b.时间<= dateadd(d,183,a.时间) and a.ID = b.ID 
) a 
where a.cn >= 3/*
id         时间
---------- -----------------------
1000103    2008-06-24 00:00:00.000
1000103    2008-08-11 00:00:00.000
1000103    2008-08-18 00:00:00.000
1000589    2008-12-18 00:00:00.000
1000589    2009-01-20 00:00:00.000
1000589    2009-03-02 00:00:00.000
1001100    2008-08-05 00:00:00.000
1001100    2008-08-19 00:00:00.000
1001100    2008-09-09 00:00:00.000
1001100    2008-09-23 00:00:00.000
1001100    2008-09-24 00:00:00.000
1001649    2010-07-15 00:00:00.000
1001649    2010-08-30 00:00:00.000
1001649    2010-10-25 00:00:00.000
1001674    2010-05-24 00:00:00.000
1001674    2010-06-11 00:00:00.000
1001674    2010-06-23 00:00:00.000
1001967    2008-09-01 00:00:00.000
1001967    2008-09-26 00:00:00.000
1001967    2008-10-09 00:00:00.000
1002505    2008-12-21 00:00:00.000
1002505    2009-02-23 00:00:00.000
1002505    2009-05-27 00:00:00.000(23 行受影响)*/

Answer 5

create table tb(ID nvarchar(10),dt datetime)
insert into tb select '1000103','2008-06-24 00:00:00.000'
insert into tb select '1000103','2008-08-11 00:00:00.000'
insert into tb select '1000103','2008-08-18 00:00:00.000'
insert into tb select '1000589','2008-12-18 00:00:00.000'
insert into tb select '1000589','2009-01-20 00:00:00.000'
insert into tb select '1000589','2009-03-02 00:00:00.000'
insert into tb select '1001100','2008-08-05 00:00:00.000'
insert into tb select '1001100','2008-08-19 00:00:00.000'
insert into tb select '1001100','2008-09-09 00:00:00.000'
insert into tb select '1001100','2008-09-23 00:00:00.000'
insert into tb select '1001100','2008-09-24 00:00:00.000'
insert into tb select '1001100','2008-10-08 00:00:00.000'
insert into tb select '1001649','2010-07-15 00:00:00.000'
insert into tb select '1001649','2010-08-30 00:00:00.000'
insert into tb select '1001649','2010-10-25 00:00:00.000'
insert into tb select '1001674','2010-05-24 00:00:00.000'
insert into tb select '1001674','2010-06-11 00:00:00.000'
insert into tb select '1001674','2010-06-23 00:00:00.000'
insert into tb select '1001967','2008-09-01 00:00:00.000'
insert into tb select '1001967','2008-09-26 00:00:00.000'
insert into tb select '1001967','2008-10-09 00:00:00.000'
insert into tb select '1002505','2008-12-21 00:00:00.000'
insert into tb select '1002505','2009-02-23 00:00:00.000'
insert into tb select '1002505','2009-05-27 00:00:00.000'
insert into tb select '1002756','2009-08-25 00:00:00.000'
insert into tb select '1002756','2009-10-30 00:00:00.000'
insert into tb select '1002756','2009-11-04 00:00:00.000'
insert into tb select '1002925','2009-10-24 00:00:00.000'
insert into tb select '1002925','2009-11-04 00:00:00.000'
insert into tb select '1002925','2010-01-24 00:00:00.000'
insert into tb select '1002925','2010-03-05 00:00:00.000'
insert into tb select '1002927','2009-11-09 00:00:00.000'
insert into tb select '1002927','2009-11-19 00:00:00.000'
insert into tb select '1002927','2009-11-19 00:00:00.000'
insert into tb select '1002986','2010-07-07 00:00:00.000'
insert into tb select '1002986','2010-07-27 00:00:00.000'
insert into tb select '1002986','2010-08-11 00:00:00.000'
insert into tb select '1003013','2010-06-01 00:00:00.000'
insert into tb select '1003013','2010-06-29 00:00:00.000'
insert into tb select '1003013','2010-07-19 00:00:00.000'
insert into tb select '1003025','2010-04-23 00:00:00.000'
insert into tb select '1003025','2010-05-08 00:00:00.000'
insert into tb select '1003025','2010-05-11 00:00:00.000'
insert into tb select '1003070','2009-09-01 00:00:00.000'
insert into tb select '1003070','2009-10-21 00:00:00.000'
insert into tb select '1003070','2009-12-03 00:00:00.000'
insert into tb select '1003104','2008-06-25 00:00:00.000'
insert into tb select '1003104','2008-07-16 00:00:00.000'
insert into tb select '1003104','2008-08-04 00:00:00.000'
insert into tb select '1003152','2009-10-24 00:00:00.000'
insert into tb select '1003152','2009-11-24 00:00:00.000'
go
select *from (
select id,dt as dt1,(
select max(dt) from(
select top 3 dt from tb where id=a.id and dt>=a.dt
)t having count(*)=3
)as dt2
from tb a
)b where dt2 is not null and datediff(dd,dt2,dt1)<183
order by id
go
drop table tb
/*
id         dt1                     dt2
---------- ----------------------- -----------------------
1000103    2008-06-24 00:00:00.000 2008-08-18 00:00:00.000
1000589    2008-12-18 00:00:00.000 2009-03-02 00:00:00.000
1001100    2008-08-05 00:00:00.000 2008-09-09 00:00:00.000
1001100    2008-08-19 00:00:00.000 2008-09-23 00:00:00.000
1001100    2008-09-09 00:00:00.000 2008-09-24 00:00:00.000
1001100    2008-09-23 00:00:00.000 2008-10-08 00:00:00.000
1001649    2010-07-15 00:00:00.000 2010-10-25 00:00:00.000
1001674    2010-05-24 00:00:00.000 2010-06-23 00:00:00.000
1001967    2008-09-01 00:00:00.000 2008-10-09 00:00:00.000
1002505    2008-12-21 00:00:00.000 2009-05-27 00:00:00.000
1002756    2009-08-25 00:00:00.000 2009-11-04 00:00:00.000
1002925    2009-10-24 00:00:00.000 2010-01-24 00:00:00.000
1002925    2009-11-04 00:00:00.000 2010-03-05 00:00:00.000
1002927    2009-11-09 00:00:00.000 2009-11-19 00:00:00.000
1002986    2010-07-07 00:00:00.000 2010-08-11 00:00:00.000
1003013    2010-06-01 00:00:00.000 2010-07-19 00:00:00.000
1003025    2010-04-23 00:00:00.000 2010-05-11 00:00:00.000
1003070    2009-09-01 00:00:00.000 2009-12-03 00:00:00.000
1003104    2008-06-25 00:00:00.000 2008-08-04 00:00:00.000(19 行受影响)*/