SQL 2005 SQL查询有关问题，望大神不吝指教
日期：2014-05-17 浏览次数：20618 次
SQL 2005 SQL查询问题，望大神不吝指教
有一个签到功能，每天每个手机号码只能签到一次，
现在需要查询指定手机号码连续三天签到和累计五天签到怎么查询吖，
求大神不吝指教
------解决方案--------------------
------解决方案--------------------
更正语法错误：
连续3天：
有一个签到功能，每天每个手机号码只能签到一次，
现在需要查询指定手机号码连续三天签到和累计五天签到怎么查询吖，
求大神不吝指教
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
*/
------解决方案--------------------
更正语法错误：
连续3天：
SELECT DISTINCT phone,0 AS ncs INTO #2 FROM #t
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
