CREATE TABLE #tempzhihu1

(UID BIGINT,

LOADTIME DATETIME)

INSERT INTO #tempzhihu1

VALUES

(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) T2

GROUP BY UID

DROP TABLE #tempzhihu1