Chinaunix首页 | 论坛 | 博客
  • 博客访问: 104585610
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类:

2008-05-25 19:19:31

 
 

(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
 
 
来自: 新客网() 详文参考:
阅读(630) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~