没有调试过,不过和上面的差不多,你自己再看看吧
select max(max_date_id ),max(sec_date_id ),id from (select id,date_id as max_date_id ,null as sec_date_id from
(select id,date,date_id, rank() over(partition by date order by date_id desc) as rk from a)
where rk=1
union all
select id,null as max_date_id,date_id as sec_date_id from
(select id,date,date_id, rank() over(partition by date order by date_id desc) as rk from a)
where rk=2
)
group by id;
select max(max_date_id ),max(sec_date_id ),id from (select id,date_id as max_date_id ,null as sec_date_id from
(select id,date,date_id, rank() over(partition by date order by date_id desc) as rk from a)
where rk=1
union all
select id,null as max_date_id,date_id as sec_date_id from
(select id,date,date_id, rank() over(partition by date order by date_id desc) as rk from a)
where rk=2
)
group by id;
select max(max_date_id),max(second_date_id),id from
(select 0 as max_date_id,max(date_id) as second_date_id,id from a b where date_id <
(select max(date_id) from a where b.id=id ) group by id
union all
select max(date_id) as max_date_id,0 as second_date_id,id from a group by id
) group by id;
select date_id,id from (select id,date_id,rank() over(partition by id order by date desc) num from a) where num<2
union
select date_id,id from (select id,date_id,rank() over(partition by id order by date desc) num from a) where num<3 and exists(select date_id,id from (select id,date_id,rank() over(partition by id order by date desc) num from a) where num<3)
create table B (fdate date,id char(10),date_id char(10));insert into B values (sysdate,1,1);
insert into B values (sysdate-1,1,2);
insert into B values (sysdate-2,1,3);
insert into B values (sysdate,2,1);
insert into B values (sysdate-1,2,2);
insert into B values (sysdate-2,2,3);
insert into B values (sysdate,3,1);
insert into B values (sysdate-1,3,2);
insert into B values (sysdate-2,3,3);
insert into B values (sysdate,4,1);
select id,fdate,date_id, rank() over(partition by fdate order by date_id desc) as rk from b;select max(max_date_id),max(sec_date_id),id from (
select id,date_id as max_date_id ,null as sec_date_id from
(select id,fdate,date_id, rank() over(partition by id order by fdate desc) as rk from b)
where rk=1
union all
select id,null as max_date_id,date_id as sec_date_id from
(select id,fdate,date_id, rank() over(partition by id order by fdate desc) as rk from b)
where rk=2
)
group by id;
SQL> select * from b;FDATE ID DATE_ID
---------- ---------- ---------
11-5月 -03 1 1
10-5月 -03 1 2
09-5月 -03 1 3
11-5月 -03 2 1
10-5月 -03 2 2
09-5月 -03 2 3
11-5月 -03 3 1
10-5月 -03 3 2
09-5月 -03 3 3
11-5月 -03 4 1
SQL> select max(max_date_id),max(sec_date_id),id from (
2 select id,date_id as max_date_id ,null as sec_date_id from
3 (select id,fdate,date_id, rank() over(partition by id order by fdate desc) as rk from b)
4 where rk=1
5 union all
6 select id,null as max_date_id,date_id as sec_date_id from
7 (select id,fdate,date_id, rank() over(partition by id order by fdate desc) as rk from b)
8 where rk=2
9 )
10 group by id;MAX(MAX_DA MAX(SEC_DA ID
---------- ---------- ----------
1 2 1
1 2 2
1 2 3
1 4