oracle为什么不更新排名,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);

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