Oracle与DB2、MySQL取前10条记录的对比

row_number() over ( partition by col1 order by col2 )比如想取出100-150条记录,按照tname排序select tname,tabtype from (select tname,tabtype,row_number() over ( order by tname ) rn from tab)where rn between 100 and 150;

select tname,tabtype from (select tname,tabtype,rownum rn from tab where rownum <= 150)where rn >= 100;

create table mynumber(id int,name varchar(10));insert into mynumber values(1,''no1'');insert into mynumber values(2,''no2'');insert into mynumber values(3,''no3'');insert into mynumber values(4,''no4'');insert into mynumber values(5,''no5'');insert into mynumber values(5,''no6'');insert into mynumber values(6,''no7'');insert into mynumber values(7,''no8'');insert into mynumber values(8,''no9'');insert into mynumber values(9,''no10'');insert into mynumber values(9,''no11'');insert into mynumber values(9,''no12'');insert into mynumber values(10,''no13'');insert into mynumber values(10,''no14'');insert into mynumber values(10,''no15'');insert into mynumber values(11,''no16'');insert into mynumber values(12,''no17'');insert into mynumber values(13,''no18'');

select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber;

select id,name from mynumber where id in (select id from (select distinct id from mynumber) tt where rownum<=10);select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber)tt where case1<=10;select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case1 between 5 and 10;

select * from mynumber where id in(select id from mynumber where rownum <=10);select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2<=10;select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2 between 5 and 10;

select id,name from mynumber where rownum <=10;select id,name from (select id,name,rownum rn from mynumber where rownum <= 10 ) where rn >= 5;select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3<=10;select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3 between 5 and 10;