--就事论事写一个
SQL> select * from test;
NAME RANK GRADE
-------------------- ---------- ----------
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9
j 10
10 rows selected
SQL>
SQL> update test a set a.grade=
2 (select decode(-1,sign(a.rank-6),1,
3 sign(a.rank-6-round((max(b.rank)-5)/2)),2,
4 3)
5 from test b);
10 rows updated
SQL> select * from test;
NAME RANK GRADE
-------------------- ---------- ----------
a 1 1
b 2 1
c 3 1
d 4 1
e 5 1
f 6 2
g 7 2
h 8 2
i 9 3
j 10 3
10 rows selected