T-SQL查询连续登陆天数

最新推荐文章于 2024-07-26 09:58:28 发布

LanSV

最新推荐文章于 2024-07-26 09:58:28 发布

阅读量348

点赞数

分类专栏： SQLServer 文章标签： sql

本文链接：https://blog.csdn.net/vincentwow/article/details/104435545

版权

SQLServer 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

DECLARE @StartTime DATE = '2020-1-1', 
        @EndTime   DATE = '2020-1-31'
--创建临时表
CREATE TABLE #log_table
(
    log_date DATE,
	uid INT
);
--插入测试数据
WHILE @StartTime <= @EndTime
BEGIN
    INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,1);
	if(@StartTime<'2020-1-8')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,2);
	if(@StartTime<'2020-1-15')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,3);
	if(@StartTime='2020-1-6')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,4);
	if(@StartTime='2020-1-7')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,4);
	if(@StartTime='2020-1-12')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,4);
	if(@StartTime='2020-1-16')
	INSERT INTO #log_table (log_date,uid)
    VALUES (@StartTime,4);
    SET @StartTime = DATEADD(DAY, 1, @StartTime);
END;
--查询
Select uid,max(cnt) as cnt
From (Select uid,Grp_No,count(*) as cnt
 From (
Select uid,log_date,(Day(log_date)-ROW_NUMBER() OVER (Partition By uid Order By uid,log_date)) as Grp_No 
From #log_table
) a 
Group By uid,Grp_No
) a 
Group By uid
--删除临时表
DROP TABLE #log_table;