有下表A:
2007-3-3 30
2007-1-1 10
2007-1-1 50
2007-1-1 20
2007-2-2 40
2007-2-2 10
要求排序如下:
1 2007-1-1 10
2 2007-1-1 20
3 2007-1-1 50
1 2007-2-2 10
2 2007-2-2 40
1 2007-3-3 30
即要求按日期排序,如日期相同,按权值排序,并加入一序列号。
这问题想了我一上午,没有结果,请高手指点
2007-3-3 30
2007-1-1 10
2007-1-1 50
2007-1-1 20
2007-2-2 40
2007-2-2 10
要求排序如下:
1 2007-1-1 10
2 2007-1-1 20
3 2007-1-1 50
1 2007-2-2 10
2 2007-2-2 40
1 2007-3-3 30
即要求按日期排序,如日期相同,按权值排序,并加入一序列号。
这问题想了我一上午,没有结果,请高手指点
create table t
( a date,
b number);
insert into t
select to_date('2007-01-01','yyyy-mm-dd'),10 from dual
union all
select to_date('2007-01-01','yyyy-mm-dd'),30 from dual
union all
select to_date('2007-01-01','yyyy-mm-dd'),20 from dual
union all
select to_date('2007-02-01','yyyy-mm-dd'),10 from dual
union all
select to_date('2007-02-01','yyyy-mm-dd'),30 from dual
union all
select to_date('2007-03-01','yyyy-mm-dd'),10 from dual;select to_char(a,'yyyy-mm-dd'),b,
row_number() over (partition by a order by a) xh
from t
order by a,b;TO_CHAR(A, B XH
---------- ---------- ----------
2007-01-01 10 1
2007-01-01 20 3
2007-01-01 30 2
2007-02-01 10 1
2007-02-01 30 2
2007-03-01 10 1
drop table t;
create table t
( a date,
b number);
insert into t
select to_date('2007-01-01','yyyy-mm-dd'),10 from dual
union all
select to_date('2007-01-01','yyyy-mm-dd'),30 from dual
union all
select to_date('2007-01-01','yyyy-mm-dd'),20 from dual
union all
select to_date('2007-02-01','yyyy-mm-dd'),10 from dual
union all
select to_date('2007-02-01','yyyy-mm-dd'),30 from dual
union all
select to_date('2007-03-01','yyyy-mm-dd'),10 from dual;select to_char(a,'yyyy-mm-dd'),b,
row_number() over (partition by a order by a) xh
from t
order by a,xh;TO_CHAR(A, B XH
---------- ---------- ----------
2007-01-01 10 1
2007-01-01 30 2
2007-01-01 20 3
2007-02-01 10 1
2007-02-01 30 2
2007-03-01 10 1
select row_number() over(partition by 日期 order by 权值),日期,权值 from 表A
order by 日期,权值
select to_char(a,'yyyy-mm-dd'),b,
row_number() over (partition by a order by b) xh
from t
order by a,xh;
TO_CHAR(A, B XH
---------- ---------- ----------
2007-01-01 10 1
2007-01-01 20 2
2007-01-01 30 3
2007-02-01 10 1
2007-02-01 30 2
2007-03-01 10 1