1樓：Kinglong
可以使用rownumber()，給每個uid的logintime排順序，然後通過date_sub得到每個日期減去序號的日期（可以理解為每次連續登入開始的前一天），再group by這個日期就能得到每次連續登入對應的天數，再取最大值就能得到最長連續登入天數。
例子：UID logintime row date_sub(logintime,row)
201 2017/1/1 1 2017/12/31
201 2017/1/2 2 2017/12/31
201 2017/1/4 3 2017/1/1
201 2017/1/5 4 2017/1/1
201 2017/1/6 5 2017/1/1
然後group by UID，date_sub（logintime,row）得到
201 2017/12/31 2
201 2017/1/1 3
再取出最大值就是最大連續登入天數
2樓：是阿青學長
select
x.uid,
max(datediff)
from
select
a.uid,
datediff(b.loadtime, a.loadtime) datediff,
(b.rk - a.rk) as rkdifffrom
select
uid,
loadtime,
row_number() over (
partition by uid
order by
loadtime
as rk
from
table
) aleft join (
select
uid,
loadtime,
row_number() over (
partition by uid
order by
loadtime
as rk
from
table
) b on a.uid = b.uid
and a.rk < b.rk
) xwhere
datediff = rkdiff
group by 1
3樓：戰鬥會
CREATE TABLE #tempzhihu1(UID BIGINT,
LOADTIME DATETIME)
INSERT INTO #tempzhihu1VALUES
(201,'2017/1/1'),
(201,'2017/1/2'),
(202,'2017/1/2'),
(202,'2017/1/3'),
(203,'2017/1/3'),
(201,'2017/1/4'),
(202,'2017/1/4'),
(201,'2017/1/5'),
(202,'2017/1/5'),
(201,'2017/1/6'),
(203,'2017/1/6'),
(203,'2017/1/7')
SELECT UID, MAX(DAYS) AS ConsecutiveDays
FROM(
SELECT UID,count(GroupingSet) AS DAYS
FROM
(SELECT UID, LOADTIME,GroupingSet = DATEADD(DAY,-ROW_NUMBER() OVER(PARTITION BY UID ORDER BY LOADTIME), LOADTIME)
FROM #tempzhihu1) T1
GROUP BY UID,GroupingSet) T2GROUP BY UID
DROP TABLE #tempzhihu1
SQL查詢指定某行某列的資料語句如何表達？
愛凡線上 sql中沒有行的固定表達，因為排序規則不一樣，其所處的行也是不一樣的。你要看這一行的資料存不存在主鍵，如果存在主鍵你只要按照主鍵檢索就可以。select from user where uid 第二行資料的ID值 SQL的每一列都有列名的，例如第5列的列名是flag 那麼你的指令可以寫成 ...
SQL 語句的查詢結果的的順序是由哪些因素決定
What is the default Order By of queries?SQL best practice to deal with default sort order MySQL row order for SELECT FROM table name mysqlops LZ的這個SQL...
如何實現MyBatis僅更新SQL語句中指定的字段？
按照 janwen提供的思路，我找到了解決辦法 資料表 CREATE TABLE qai entity id CHAR 10 NOT NULL,value INT NOT NULL DEFAULT 0,create time TIMESTAMP NOT NULL,PRIMARY KEY id 將以上...