有一张表记录表record,有字段userID(用户ID),applyTime(应用时间),value(值)字段。
正常记录,例如:
2008-1-1 0:0:0
2008-1-1 1:0:0
...
2009-1-1 0:0:0
每间隔1小时,产生一条数据
要求查询1年内,连续3小时没有数据的用户???
正常记录,例如:
2008-1-1 0:0:0
2008-1-1 1:0:0
...
2009-1-1 0:0:0
每间隔1小时,产生一条数据
要求查询1年内,连续3小时没有数据的用户???
not exists(select 1 from tb where userID = t.userID and datediff(hh,t.applyTime,applyTime) = 1) and
not exists(select 1 from tb where userID = t.userID and datediff(hh,t.applyTime,applyTime) = 2) and
not exists(select 1 from tb where userID = t.userID and datediff(hh,t.applyTime,applyTime) = 3)
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 1) and
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 2) and
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 3)
;with cte as(
select rank() over(partition by userid order by applytime desc)as ranknum,*
from record
where YEAR(applytime)=2010 --此处是查询的年份
)
select a.*,b.*
from cte a inner join cte b on a.userID=b.userID and a.ranknum=b.ranknum-1
where datediff(d,a.applytime,b.applyTime )>=20 --此处为20天,你想要几天就改成几
create table record(userID int,applyTime datetime,value int)
;with cte as(
select rank() over(partition by userid order by applytime desc)as ranknum,*
from record
where YEAR(applytime)=2010 --此处是查询的年份
)
select a.*,b.*
from cte a inner join cte b on a.userID=b.userID and a.ranknum=b.ranknum-1
where datediff(hh,a.applytime,b.applyTime )>=20 --此处为20小时,你想要几小时就改成几
另外,不知道楼主是否要考虑一年的开始几天或结束几天用户没有数据的情况?
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 1) and
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 2) and
not exists(select 1 from tb where userID = t.userID and datediff(yy,t.applyTime,applyTime) = 0 and datediff(hh,t.applyTime,applyTime) = 3)