oracle降序查一月份,Oracle 子查询，按降序排列，取前n条 | 学步园

比如，有一个student 表：

id             student_name

1                                  s1

2                                  s2

03                                s3

04                                s4

05                               s5

06                              s6

07                                s7

08                             s8

09                              s9

这些id 不一致，比如我想把id 变成 1， 2， 3(而不用03)，4， 5 ， 6, 7， 8， 9

可执行如下操作：

update  student a set a.id =(select substr(b.id,-1) from student b where b.id=a.id)

where a.id >2;

commit;

之后结果变为：

id             student_name

1                                  s1

2                                  s2

3                               s3

4                               s4

5                               s5

6                               s6

7                              s7

8                                s8

9                               s9

之后，想通过子查询的方式，按倒序显示前6条记录：

select * from (select * from student order by id desc)

where rownum <= 6;

结果为：

id             student_name

9                    s9

8                    s8

7                    s7

6                    s6

5                    s5

4                    s4