oracle排名次的问题

一张表内有一个字段a,记录了从0－100的整数，现要根据a来排名次,然后把名次写在字段b里   
 
如： 
 
id        a          b
1          2          0 
2          5          0   
3          5          0
4          6          0
5          3          0
 
排名次的结果应该是 
 
b(名次)    id    a 
1          4      6 
2          2      5 
3          3      5 
4          5      3 
5          1      2 
 
这样的SQL语句怎么写最好呢？

 

解答：

SQL> select *from ts1 ;

        ID          A          B
---------- ---------- ----------
         1          2          0
         2          5          0
         3          5          0
         4          6          0
         5          3          0
SQL> update ts1 t
  2  set t.b = (select num from
  3      (select row_number()over(order by a asc) as num, rowid as rid from ts1
)
  4      where t.rowid = rid);

已更新5行。
SQL> select *from ts1 ;

        ID          A          B
---------- ---------- ----------
         1          2          1
         2          5          3
         3          5          4
         4          6          5
         5          3          2
如果需要逆序的排序，只需要修改
SQL> update ts1 t
  2  set t.b = (select num from
  3      (select row_number()over(order by a desc) as num, rowid as rid from ts1
 )
  4      where t.rowid = rid);

已更新5行。
SQL> select b, id ,a from ts1 order by 1 ;

         B         ID          A
---------- ---------- ----------
         1          4          6
         2          2          5
         3          3          5
         4          5          3
         5          1          2

分析：

select row_number()over(order by a desc) as num, rowid as rid from ts1
num --- 取得排名, row_number()over(...)分析函数的用法可以在网上搜索
rowid -- 数据行的实际物理地址

update ts1 t
set t.b =
  (select num from
     (select row_number()over(order by a desc) as num, rowid as rid from ts1 )
   where t.rowid = rid);

首先确定数据的排名和物理地址,然后根据物理地址更新排名