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

来源：pcdog 作者：佚名出处：巧巧读书 2008-01-09 进入讨论组

关键词：
update
oracle
mysql
db2

(3)取前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;

Db2示例
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

(1) 取前10条不同id记录，假如最后1条记录的ID依然有相同的，那么全部取出来。

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

(2)取前10条记录，假如第10条记录的ID 还有相同的，那么全部取出来。

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

(3)取前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

Mysql示例：

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

转载保留：http://www.qqread.com/db2/b391942.html