create table the_test
(
id int identity(1,1) primary key not null,
logindate datetime unique not null
)
insert the_test
select '2015-01-27 10:26:24.547' union
select '2015-01-27 14:43:05.533' union
select '2015-01-28 11:35:20.177' union
select '2015-01-29 17:41:18.557' union
select '2015-01-29 17:42:18.557' union
select '2015-01-29 17:43:18.557' union
select '2015-01-30 16:05:52.567' union
select '2015-02-02 17:05:20.950' union
select '2015-02-03 09:23:39.713'
...全文
15915打赏收藏
【谢谢】关于连续登录天数的获取。
现在的数据库中,每次登录,都会插入一条数据到登录记录表。 我的需求是,需要在今天登录之后,可以看到我已经连续登录了几天。 头疼了一下午,百度了好久,着实没有思路,也没有找到适合我的 SQL。 只能麻烦各位了,非常感谢。。 下面是为各位老大准备好了建表语句和测试数据,请享用。。 create table the_test ( id int identity(1,1) primary key not null, logindate datetime unique not null ) insert
--如果只查单个人
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,GETDATE())=T1.number
AND UserId='张三的ID' --条件统一加到这边
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
CTE方式,其实就是递归,从昨天找起,每找到一个,连续天数就加1,递归到哪个没有昨天的昨天,这时的天数就是当前的连续登录天数,不过,我的语句还得调下
SQL2000有效的方式,则更好理解了,效率也更快
大概构思是这样,从今天算起,找出最近第一个没有登录的天数,这个和现在相距的天数就是连续登录天数
主要逻辑体现在 TOP 1 T2.logindate IS NULL ORDER BY T1.number
这些东西,你熟悉了SQL之后,就明白了,我口才不好,可以描述得不是很清楚
[/quote]
好的,非常感谢!
CTE方式,其实就是递归,从昨天找起,每找到一个,连续天数就加1,递归到哪个没有昨天的昨天,这时的天数就是当前的连续登录天数,不过,我的语句还得调下
SQL2000有效的方式,则更好理解了,效率也更快
大概构思是这样,从今天算起,找出最近第一个没有登录的天数,这个和现在相距的天数就是连续登录天数
主要逻辑体现在 TOP 1 T2.logindate IS NULL ORDER BY T1.number
这些东西,你熟悉了SQL之后,就明白了,我口才不好,可以描述得不是很清楚
--CTE方式小调一下
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,@loginTime)=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T1.logindate,T2.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC
--以下SQL2000有效(建议用以下方式)
--从指定日期查询
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,@loginTime)=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--直接查询当前连续登录天数
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,GETDATE())=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--CTE方式小调一下
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,@loginTime)=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T1.logindate,T2.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC
--以下SQL2000有效(建议用以下方式)
--从指定日期查询
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,@loginTime)=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--直接查询当前连续登录天数
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,GETDATE())=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--CTE方式小调一下
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,@loginTime)=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T1.logindate,T2.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC
--以下SQL2000有效(建议用以下方式)
--从指定日期查询
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,@loginTime)=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--直接查询当前连续登录天数
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,GETDATE())=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--CTE方式小调一下
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,@loginTime)=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T1.logindate,T2.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC
--以下SQL2000有效(建议用以下方式)
--从指定日期查询
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,@loginTime)=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
--直接查询当前连续登录天数
SELECT TOP 1 T1.number
FROM master..spt_values T1
LEFT JOIN the_test T2 ON DATEDIFF(DAY,T2.logindate,GETDATE())=T1.number
WHERE T1.type='P'AND T1.number>0 AND T2.logindate IS NULL
ORDER BY T1.number
DECLARE @loginTime DATETIME
SET @loginTime=GETDATE()
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,@loginTime)=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T2.logindate,T1.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC
固定查询当前连续登录天数
;WITH CTE AS(
SELECT TOP 1 logindate,1 Times
FROM the_test
WHERE DATEDIFF(DAY,logindate,GETDATE())=1
UNION ALL
SELECT T1.logindate,T2.Times+1
FROM the_test T1
JOIN CTE T2 ON DATEDIFF(DAY,T2.logindate,T1.logindate)=1
)
SELECT TOP 1 Times
FROM CTE
ORDER BY Times DESC