sql 得到连续签到天数及总签到天数

DECLARE @days INT SET @days = 0 DECLARE @curr_day DATETIME --创建day用于储存连续签到日期SET

@curr_day = GETDATE() --curr_day为当前日期

WHILE EXISTS(SELECT null FROM Mall_MemberSign_Log WHERE [MemberId]=11514 AND DATEDIFF(dd,SignTime,@curr_day) = 1) --进行循环判断之前一天是否签到

BEGIN

SET @days = @days + 1

SET@curr_day = DATEADD(dd, -1, @curr_day)

END

if((select 1 from Mall_MemberSign_Log where MemberId=11514 and convert(varchar(10), getdate(), 23)=convert(varchar(10), SignTime, 23)) is not null) --判断今天是否签到了

BEGIN SET @days = @days + 1

END

select @days as ContinuitySignDay ,COUNT(SignTime) as SignCount ,SUM(Integral) as TotalIntegral from Mall_MemberSign_Log where [MemberId]=11514 --得到连续签到天数，总签到次数，签到获得的积分