此文章是 https://www.cnblogs.com/xiandedanteng/p/12525063.html 一文的后续,解决问题的关键还是在于basicdate,基本上把上一篇看懂了,这一篇也就很快懂了。
建表:
create table hy_login_flow( id number(4,0) not null primary key, name nvarchar2(20) not null, logindate date not null)
充值:
insert into hy_login_flow(id,name,logindate) values('1','A001',to_date('2020-01-01','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('2','A001',to_date('2020-01-02','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('11','A001',to_date('2020-01-03','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('3','A001',to_date('2020-01-05','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('4','A001',to_date('2020-01-06','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('5','B001',to_date('2020-01-01','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('6','B001',to_date('2020-01-02','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('7','B001',to_date('2020-01-05','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('8','C001',to_date('2020-01-01','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('9','C001',to_date('2020-01-02','yyyy-MM-dd')); insert into hy_login_flow(id,name,logindate) values('10','D001',to_date('2020-01-07','yyyy-MM-dd'));
查询语句:
select b.name,count(*) as cnt,min(b.logindate) as fromDate,max(b.logindate) as toDate from (select a.*,a.logindate-a.seq as basicdate from (select row_number() over (partition by name order by logindate) as seq,name,logindate from hy_login_flow) a) b group by b.name,b.basicdate order by b.name,b.basicdate
查询结果:
--2020年3月21日--