sql语句如何查询指定日期共几天 – java – 前端

CREATE TABLE #tempzhihu1

(UID BIGINT,

LOADTIME DATETIME)

INSERT INTO #tempzhihu1

VALUES

(201,’/1/1′),

(201,’/1/2′),

(202,’/1/2′),

(202,’/1/3′),

(203,’/1/3′),

(201,’/1/4′),

(202,’/1/4′),

(201,’/1/5′),

(202,’/1/5′),

(201,’/1/6′),

(203,’/1/6′),

(203,’/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