ref: https://bbs.csdn.net/topics/392243867
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
mysql 8:
create table Tmp_Data ( List_ID int , UID varchar(10), LoadTime datetime );
INSERT INTO Tmp_Data (UID,LoadTime) values ('201','2017/01/01'),
('202','2017/01/02'),
('202','2017/01/03'),
('203','2017/01/03' ),
('201','2017/01/04'),
('202','2017/01/04'),
('201','2017/01/05'),
('202','2017/01/05'),
('201','2017/01/06'),
('203','2017/01/06'),
('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 version();
select * from Tmp_Data;
#2
更多评论
今日阅读排行
一周阅读排行