sql获取用户连接签到的时间

最新推荐文章于 2022-08-09 10:52:06 发布

碧山云游

最新推荐文章于 2022-08-09 10:52:06 发布

阅读量170

点赞数

分类专栏： SQL与数据库文章标签： sql

本文链接：https://blog.csdn.net/qq_41617901/article/details/125324172

版权

SQL与数据库专栏收录该内容

39 篇文章 2 订阅

订阅专栏

以下是查询的sql:

drop table  if  exists   #t
create table #t
(keyId int identity, actionDate datetime)
insert into #t(actionDate) select distinct InputDate from   Customer_main_PointRecord  where [OrderType]='Signin'and datediff(day,GETDATE(),InputDate)<0   and CMPAccount='10731'  and Inputor='12001'
 order BY InputDate 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) signinday

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