假如一张表中有以下数据
id num date
1 2 2011-08-01
1 3 2011-08-02
1 5 2011-08-03
2 1 2011-08-01
2 3 2011-08-02
2 5 2011-08-03我想改成这样的
id num date
1 1 2011-08-01
1 2 2011-08-02
1 3 2011-08-03
2 1 2011-08-01
2 2 2011-08-02
2 3 2011-08-03
num根据同一个ID的不同时间给他重新排序。
id num date
1 2 2011-08-01
1 3 2011-08-02
1 5 2011-08-03
2 1 2011-08-01
2 3 2011-08-02
2 5 2011-08-03我想改成这样的
id num date
1 1 2011-08-01
1 2 2011-08-02
1 3 2011-08-03
2 1 2011-08-01
2 2 2011-08-02
2 3 2011-08-03
num根据同一个ID的不同时间给他重新排序。
select '1' id ,to_date('2011-08-01','yyyy-MM-dd') d from dual
union all
select '1' id ,to_date('2011-08-02','yyyy-MM-dd') d from dual
union all
select '1' id ,to_date('2011-08-03','yyyy-MM-dd') d from dual
union all
select '2' id ,to_date('2011-08-01','yyyy-MM-dd') d from dual
union all
select '2' id ,to_date('2011-08-02','yyyy-MM-dd') d from dual
union all
select '2' id ,to_date('2011-08-03','yyyy-MM-dd') d from dual
)select t.id,row_number() over(partition by t.id order by t.d),t.d
from t
只要这句就行了 select t.id,row_number() over(partition by t.id order by t.d),t.d
from t写那个是方便直接看下结果·~