SELECT DISTINCT phone,0 AS ncs INTO #2 FROM #t
DECLARE @endtime datetime
DECLARE @begintime datetime
select @endtime = max(gettime) FROM #t
PRINT @endtime
SELECT @begintime= min(gettime) FROM #t
WHILE @begintime<=@endtime
BEGIN
UPDATE a SET a.ncs=CASE WHEN a.phone IN(SELECT phone
FROM #t WHERE gettime=@begintime ) THEN ncs+1
WHEN a.ncs=3 THEN 3 ELSE 0 END
FROM #2 a
SET @begintime=@begintime+1
END
SELECT * FROM #2 WHERE ncs>=3
--> 测试数据:@t (模拟了15条数据)
declare @t table(id varchar(2),Phone varchar(11),GetTime datetime)
insert @t
select '1','13100000000','2013-07-01' union all
select '2','13100000000','2013-07-02' union all
select '3','13100000000','2013-07-04' union all
select '4','13100000001','2013-07-01' union all
select '5','13100000001','2013-07-02' union all
select '6','13100000001','2013-07-03' union all
select '7','13100000003','2013-07-05' union all
select '8','13100000003','2013-07-02' union all
select '9','13100000002','2013-07-01' union all
select '10','13100000002','2013-07-01' union all
select '11','13100000002','2013-07-02' union all
select '12','13100000002','2013-07-03' union all
select '13','13100000003','2013-07-01' union all
select '14','13100000003','2013-07-04' union all
select '15','13100000003','2013-07-06'
--累计5条的
select Phone from @t group by Phone having(count(*)>=5)
/*
手机
-----------
13100000003
*/
--连续3天的
;with maco as
(
select *,
ROW_NUMBER() over (partition by Phone order by GetTime) as rid,cast(GetTime-(select min(GetTime) from @t)+1 as int) as cnt
from @t
)
select Phone from maco group by Phone,cnt-rid having(count(*)>=3)
/*
Phone
-----------
13100000002
13100000001
13100000003
*/
还未能解决,请不吝赐教[/quote]
还望可以加下QQ指教一番:1842989293[/quote]
我是给这个表加了一列 次数,用语记录后面的连续次数,
不好意思,工作中,不能用Q,你有问题可以留言帖子,我得空了会关注你的问题的[/quote]
连续3天:SELECT DISTINCT phone,0 AS ncs INTO #2 FROM tb
INTO 后面#2代表什么? tb应该是我要查询的表
[quote=引用 8 楼 xiaoqi7456 的回复:]
连续3天:SELECT DISTINCT phone,0 AS ncs INTO #2 FROM tb
DECLARE @endtime datetime
DECLARE @begintime datetime
select @endtime = max(gettime) FROM tb
SELECT @begintime= min(gettime) FROM tb
WHILE @begintime<@begintime
BEGIN
UPDATE a SET a.ncs=CASE WHEN a.sspbh IN(SELECT sspbh
FROM tb WHERE gettime=@begintime ) AND THEN ncs+1
WHEN a.ncs=3 THEN 3 ELSE 0 END
FROM #2 a
SET @begintime=@begintime+1
END
SELECT * FROM #2 WHERE ncs>=3
累计5天: select phone ,count(1) from tb
group by phone having count(1)>=5
连续3天:SELECT DISTINCT phone,0 AS ncs INTO #2 FROM tb
phone后面字段0是什么?
连续3天:SELECT DISTINCT phone,0 AS ncs INTO #2 FROM tb
DECLARE @endtime datetime
DECLARE @begintime datetime
select @endtime = max(gettime) FROM tb
SELECT @begintime= min(gettime) FROM tb
WHILE @begintime<@begintime
BEGIN
UPDATE a SET a.ncs=CASE WHEN a.sspbh IN(SELECT sspbh
FROM tb WHERE gettime=@begintime ) AND THEN ncs+1
WHEN a.ncs=3 THEN 3 ELSE 0 END
FROM #2 a
SET @begintime=@begintime+1
END
SELECT * FROM #2 WHERE ncs>=3
累计5天: select phone ,count(1) from tb
group by phone having count(1)>=5
还未能解决,请不吝赐教[/quote]