Oracle与DB2、MySQL取前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;

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<=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) 
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<=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) 
as tt where case2 between 5 and 10

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 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) 
as tt where case3 between 5 and 10

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