比如,有一个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