--求出表F01中每个人在每家单位的缴费最小时间和缴费最大时间 ，按时间升序排序
--创建表
create table F01
(
AAC001 number(20),--人员编号
AAB001 number(20),--单位编号
AAE003 number(6)--缴费时间
);
--插入数据
insert into F01(aac001,aab001,aae003)values(1001,2001,202003);
insert into F01(aac001,aab001,aae003)values(1001,2001,202004);
insert into F01(aac001,aab001,aae003)values(1001,2002,202005);
insert into F01(aac001,aab001,aae003)values(1001,2002,202006);
insert into F01(aac001,aab001,aae003)values(1001,2001,202007);
insert into F01(aac001,aab001,aae003)values(1001,2007,202008);
insert into F01(aac001,aab001,aae003)values(1001,2007,202009);
insert into F01(aac001,aab001,aae003)values(1002,2001,202003);
insert into F01(aac001,aab001,aae003)values(1002,2001,202004);
--数据处理
SELECT a.*, rank1 - rank2 as rank3
FROM (SELECT a.*,
row_number() over(partition by aab001 ORDER BY aae003) rank2
FROM (SELECT a.aab001,
a.aac001,
a.aae003,
row_number() over(ORDER BY aae003) rank1
FROM F01 a) a) a；
SELECT aac001, aab001, min(aae003) min_aae003, max(aae003) max_aae003
FROM (SELECT a.*, rank1 - rank2 as rank3
FROM (SELECT a.*,
row_number() over(partition by aab001 ORDER BY aae003) rank2
FROM (SELECT a.aab001,
a.aac001,
a.aae003,
row_number() over(ORDER BY aae003) rank1
FROM F01 a) a) a)
GROUP BY aab001, aac001, rank3
ORDER BY min_aae003;
