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

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 * 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) as tt where case1<=10

select * from mynumber where id in(select distinct id from mynumber fetch first 10 rows only)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) as tt where case1<=10select * 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) as tt where case1 between 5 and 10

select * from mynumber where id in(select id from mynumber fetch first 10 rows only)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) as tt where case2<=10select * 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) as tt where case2 between 5 and 10

select id from mynumber fetch first 10 rows onlyselect * 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) as tt where case3<=10select * 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) as tt where case3 between 5 and 10

select id from mytable order by update_date desc limit 0,10