mssql sqlserver sql 连续签到的天数获取,及排序脚本编写方法分享


摘要:
下文将分享sql脚本获取用户连续签到额天数,如下所示:
实验环境:sql server 2008 R2


实现思路:
采用遍历循环的方式,依次从大到小的比对用户是否签到

 
create table test(userId int,actionDate datetime)
insert into test (userId,actionDate)
values (1,'2018-08-01'),(2,'2018-08-01'),(2,'2018-08-02'),
	   (2,'2018-08-3'),(2,'2018-09-12'),(2,'2018-09-13'),
	   (2,'2018-09-13'),(2,'2018-09-13'),(2,'2018-09-14'),
       (2,'2018-09-15'),(3,'2018-09-10'),(3,'2018-09-14'),
       (3,'2018-09-15'),(3,'2018-09-15'),(3,'2018-09-15'),
       (3,'2018-09-15'),(4,'2018-09-13')
go

---例:查询userId等于2的用户连续签到的天数
create table #t
(keyId int identity,actionDate datetime)
insert into #t(actionDate) select distinct  actionDate from test where userId =30 order by actionDate  desc 
  
 
---采用遍历的方式生成用户连续签到的天数 start 

declare @i int ,@imax int ,@startDate datetime
set @i=1 
select @imax = max(keyId),@startDate =max(actionDate) from #t 

  

while @i <@imax
begin  
   set @startDate = dateadd(day,-1,@startDate)
  set @i =@i+1
  
  if not exists(
    select null from #t where keyId =@i and actionDate =@startDate
  )
  begin
    set @i=@i-1 
    break;
  end  
end
 
if @imax is null
begin 
 set @i=0
end 
 

select   '连续签到天数:'+convert(varchar(20),@i)


---采用遍历的方式生成用户连续签到的天数 end 

 
go
truncate table #t
drop     table #t 


truncate table test
drop     table test 
mssql_sqlserver_连续签到

mssql_sqlserver_连续签到天数获取方法