sql查询连续签到天数 - 数据库知识,sqlserver,mysql知识积累 - CSS模版园-免费网站模版下载
当前位置 CSS模版园 >> 网站制作 >> 数据库 >> sql查询连续签到天数

原创sql查询连续签到天数

sql查询连续签到天数

DECLARE @now DATETIME  = GETDATE(); --计算日期,默认今天
DECLARE @count INT;  --连续签到天数
SELECT  @count = COUNT(1)
FROM    ( SELECT    0 [Row] ,0 DayCount
          FROM      表
          WHERE     UserName = 'cssteach.com'
                    AND DATEDIFF(DAY, 时间字段, @now) = 0  --当天的签到记录 
          UNION
          SELECT    ROW_NUMBER() OVER ( ORDER BY CreateDate DESC ) [Row] ,    --排序字段 
                    DATEDIFF(DAY, 时间字段, @now) DayCount  --签到时间对比今天的差值
          FROM      表
          WHERE     UserName = 'cssteach.com'
                    AND DATEDIFF(DAY, 时间字段, @now) > 0  --条件排除今天的签到记录 
        ) T
WHERE   [Row] = DayCount; 

SELECT  @count;

事例

sql查询连续签到天数

CREATE TABLE UserTemp ( 
	UserID INT IDENTITY(1,1), 
	UserName     VARCHAR(10),   --姓名  
	CreateDate	DATETIME
);  

INSERT INTO UserTemp VALUES('张三','2017-06-23 10:09:24');  
INSERT INTO UserTemp VALUES('张三','2017-06-22 10:09:24');  
INSERT INTO UserTemp VALUES('张三','2017-06-21 10:09:24');  
INSERT INTO UserTemp VALUES('张三','2017-06-19 10:09:24');  
INSERT INTO UserTemp VALUES('张三','2017-06-18 10:09:24');  


DECLARE @now DATETIME  = '2017-06-23';
 --计算日期,默认今天
DECLARE @count INT; 
SELECT  @count = COUNT(1)
FROM    ( SELECT    0 [Row] , 0 DayCount
          FROM      UserTemp
          WHERE     UserName = '张三'
                    AND DATEDIFF(DAY, CreateDate, @now) = 0  --条件排除今天的签到记录 
          UNION
          SELECT    ROW_NUMBER() OVER ( ORDER BY CreateDate DESC ) [Row] ,    --排序字段 
                    DATEDIFF(DAY, CreateDate, @now) DayCount  --签到时间对比今天的差值
          FROM      UserTemp
          WHERE     UserName = '张三'
                    AND DATEDIFF(DAY, CreateDate, @now) > 0  --条件排除今天的签到记录 
        ) T
WHERE   [Row] = DayCount; 

SELECT  @count;

DROP TABLE UserTemp

sql查询连续签到天数

DECLARE @now DATETIME  = GETDATE(); --计算日期,默认今天
SELECT   CreateDate, 0 [Row] ,0 DayCount
          FROM      UserTemp
          WHERE     UserName = '张三'
                    AND DATEDIFF(DAY, CreateDate, @now) = 0  --今天的签到记录 
          UNION
          SELECT    CreateDate,ROW_NUMBER() OVER ( ORDER BY CreateDate DESC ) [Row] ,    --排序字段 
                    DATEDIFF(DAY, CreateDate, @now) DayCount  --签到时间对比今天的差值
          FROM      UserTemp
          WHERE     UserName = '张三'
                    AND DATEDIFF(DAY, CreateDate, @now) > 0  --条件排除今天的签到记录

原创文章内容原创,转载请注明出处:cssteach.com

声明本站所有资源仅供学习与参考,请勿用于商业用途。转载请注明来自:cssteach.com

原文http://cssteach.com/html/show-14-165.html

若文章内容有错误或无法下载,请在回复中说明(可无需登录,直接评论)

最新文章

图文

热门网站模版