22,210
社区成员
发帖
与我相关
我的任务
分享
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
create table #Tmp_Data
(
List_ID int identity(1,1),
UID varchar(10),
LoadTime datetime
)
INSERT INTO #Tmp_Data
Select '201','2017/01/01' union all
Select '201','2017/01/02' union all
Select '202','2017/01/02' union all
Select '202','2017/01/03' union all
Select '203','2017/01/03' union all
Select '201','2017/01/04' union all
Select '202','2017/01/04' union all
Select '201','2017/01/05' union all
Select '202','2017/01/05' union all
Select '201','2017/01/06' union all
Select '203','2017/01/06' union all
Select '203','2017/01/07'
Select UID,max(cnt) as cnt
From (
Select UID,Grp_No,count(*) as cnt
From (
Select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as Grp_No
From #Tmp_Data
) a
Group By UID,Grp_No
) a
Group By UID
SELECT [UID] , COUNT(*) AS 记录数
FROM ( SELECT * ,RN1 - ROW_NUMBER() OVER ( PARTITION BY [UID] ORDER BY RN1 ) AS Grp
FROM ( SELECT * , ROW_NUMBER() OVER ( ORDER BY RAND() ) AS RN1 FROM #t ) AS t
) AS t2
GROUP BY [UID] , t2.Grp order by min ([RN1]);
#T 换成你的表名