SQL 2005 SQL查询问题，望大神不吝指教
有一个签到功能，每天每个手机号码只能签到一次，
现在需要查询指定手机号码连续三天签到和累计五天签到怎么查询吖，
求大神不吝指教
--> 测试数据：@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
*/
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
