问题描述

请问: 查询到今天为止连续3天登陆的用户 (sql语句)

KeyId UserID Time
2 131 2014-12-29
3 131 2014-12-30
4 102 2014-12-23
5 30 2014-12-29
6 30 2014-12-28
7 30 2014-12-27
8 19 2014-12-29
9 7 2014-12-29
10 8 2014-12-30
用户登陆表

查询出以前(前天)到今天, 连续3天都登陆过的用户的数量
请问sql怎么写, 谢谢

解决方案

数据库都有日期差函数，然后按照这个条件，并结合count函数，得到数量是否为3

解决方案二：

http://www.cnblogs.com/TeyGao/p/3628950.html

解决方案三：

以下代码， 亲测可用

 IF OBJECT_ID('T_ask') IS NOT NULL
DROP TABLE T_ask
GO
CREATE TABLE T_ask(
    KeyId INT,
    UserId INT,
    [Time] DATETIME
)
GO
INSERT INTO T_ask
(
    KeyId,
    UserId,
    [Time]
)
select 2, 131, '2014-12-29' UNION
select 3, 131 , '2014-12-30' UNION
select 4, 102 , '2014-12-23' UNION
select 5, 30 , '2014-12-29' UNION
select 6, 30 , '2014-12-28' UNION
select 7, 30 , '2014-12-27' UNION
select 8, 19 , '2014-12-29' UNION
select 9, 7 , '2014-12-29' UNION
select 10, 8 , '2014-12-30'
;WITH t as (
    --排除一天多次登录
    SELECT UserId,CONVERT(DATETIME, CONVERT(varchar(100), [Time], 112)) AS [Time]
    FROM T_ask ta
    GROUP BY ta.UserId,CONVERT(varchar(100), [Time], 112)
)
,t2 AS (
    SELECT a.UserId
        , a.TIME
        , DATEDIFF(DAY, (SELECT MIN(b.Time) FROM t B WHERE a.UserId=b.UserId) , a.time) AS X
    FROM t AS A
)
SELECT * FROM T_ask WHERE userId IN (
    SELECT userId FROM t2 A WHERE EXISTS(
        SELECT 1 FROM t2 B WHERE a.userId=b.UserId AND b.X-a.X=1
    )
    and EXISTS(
        SELECT 1 FROM t2 B WHERE a.userId=b.UserId AND b.X-a.X=2
    )
)
ORDER BY userId, [Time]

解决方案四：

select * from login A where exists( select 1 from login B where A.user_id = B.user_id and A.time = B.time + 1) and
exists(select 1 from login C where A.user_id = C.user_id and A.time = C.time + 2)