用rownum是可以的。举例如下:SQL> select bh from fxx_code where sx='0' order by bh;BH -------------------- 01J 02J 03J 04J 05J 06J 07J已选择7行。取最后3行: SQL> select bh from 2 ( 3 select rownum r,tt.* from 4 ( 5 select * from 6 ( 7 select rownum rn,t.* from 8 (select bh from fxx_code where sx='0' order by bh 9 ) t 10 ) a order by rn desc 11 ) tt where rownum<=3 12 ) b order by rn 13 ;BH -------------------- 05J 06J 07J已选择3行。取最后5行: SQL> select bh from 2 ( 3 select rownum r,tt.* from 4 ( 5 select * from 6 ( 7 select rownum rn,t.* from 8 (select bh from fxx_code where sx='0' order by bh 9 ) t 10 ) a order by rn desc 11 ) tt where rownum<=5 12 ) b order by rn 13 ;BH -------------------- 03J 04J 05J 06J 07J已选择5行。
写一个比较容易理解滴 select * from fxx_code where rownum<=(select count(1) from fxx_code) minus select * from fxx_code where rownum<=(select count(1)-需要选的行数 from fxx_code)
上面说的都没错,如果你要取最后XX行,你就order by ID desc 然后再用rownum<=XX 就可以了。
可以用ROW_NUMBER()OVER(ORDER BY ... PARTITION BY ...)
分析函数比较简单,SQL> select bh from ( 2 select rank() over (order by bh desc) rk,bh from fxx_code where sx='0') 3 where rk<=3 4 order by bh;BH -------------------- 05J 06J 07J已选择3行。 SQL> select bh from ( 2 select rank() over (order by bh desc) rk,bh from fxx_code where sx='0') 3 where rk<=4 4 order by bh;BH -------------------- 04J 05J 06J 07J已选择4行。
--------------------
01J
02J
03J
04J
05J
06J
07J已选择7行。取最后3行:
SQL> select bh from
2 (
3 select rownum r,tt.* from
4 (
5 select * from
6 (
7 select rownum rn,t.* from
8 (select bh from fxx_code where sx='0' order by bh
9 ) t
10 ) a order by rn desc
11 ) tt where rownum<=3
12 ) b order by rn
13 ;BH
--------------------
05J
06J
07J已选择3行。取最后5行:
SQL> select bh from
2 (
3 select rownum r,tt.* from
4 (
5 select * from
6 (
7 select rownum rn,t.* from
8 (select bh from fxx_code where sx='0' order by bh
9 ) t
10 ) a order by rn desc
11 ) tt where rownum<=5
12 ) b order by rn
13 ;BH
--------------------
03J
04J
05J
06J
07J已选择5行。
select * from fxx_code where rownum<=(select count(1) from fxx_code)
minus
select * from fxx_code where rownum<=(select count(1)-需要选的行数 from fxx_code)
就可以了。
2 select rank() over (order by bh desc) rk,bh from fxx_code where sx='0')
3 where rk<=3
4 order by bh;BH
--------------------
05J
06J
07J已选择3行。
SQL> select bh from (
2 select rank() over (order by bh desc) rk,bh from fxx_code where sx='0')
3 where rk<=4
4 order by bh;BH
--------------------
04J
05J
06J
07J已选择4行。