SQL> with t as (select 1 as id, 'A' as ll ,2008 as year from dual union all
2
select 2 as id, 'A' as ll ,2008 as year from dual union all
3
select 3 as id, 'A' as ll ,2008 as year from dual union all
4
select 2 as id, 'A' as ll ,2007 as year from dual union all
5
select 3 as id, 'B' as ll ,2007 as year from dual )
6select id,ll,year from t
7order by year desc,decode(lead(ll,1,null)over(partition by id order by year desc),ll,2,null,3,1) ,id
8;
ID LL YEAR
---------- -- ----------
3 A2008
2 A2008
1 A2008
2 A2007
3 B2007
LZ看看对不对,如果再有 2006 年的话应该是什么样的呢
|