Oracle与DB2、MySQL取前10笔记实的比拟2

 
 

(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
 
 
来自: 新客网(www.xker.com) 详文参考：http://www.xker.com/page/e2008/0128/46627_2.html


版权声明： 原创作品，答应转载，转载时请务必以超链接形式标明文章 原始因由 、作者信息和本声明。不然将究查轨则责任。