DB2、oralce、mysql数据库取前十条记录代码
来源:互联网 发布:hpe软件学院 编辑:程序博客网 时间:2024/05/22 07:53
-
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
case 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
case 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
case 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
oralce 例子
1. 最好还是利用分析函数
row_number() over ( partition by col1 order by col2 )
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;
2. 直接使用rownum 虚列
select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序.
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 id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber;
cas1: 取前10条不同id记录,如果最后1条记录的ID 还有相同的,都要取出来。
select id,name from mynumber where id in (select id from (select distinct id from mynumber) tt where rownum<=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 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) tt where case1 between 5 and 10;
case 2:取前10条记录,如果第10条记录的ID 还有相同的,都要取出来。
select * from mynumber where id in(select id from mynumber where rownum <=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 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) tt where case2 between 5 and 10;
case 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;
Mysql 例子
select id from mytable order by update_date desc limit 0,10
本文来自: IT知道网(http://www.itwis.com) 详细出处参考:http://www.itwis.com/html/database/base/20071230/778.html
- DB2、oralce、mysql数据库取前十条记录代码
- DB2 oralce mysql 取前十条记录对比
- DB2 oralce mysql 取前十条记录对比
- oralce, DB2 , sql server , mysql各数据库字段总结
- oralce, DB2 , sql server , mysql各数据库字段总结
- java连接各种数据库(Oralce,MySQL,SQLServer,DB2,Infomix,SyBase,PostgreSQL)方法
- Oralce、DB2、SQL Server、MySQL 数据类型总结
- java连接Oracle、Mysql、DB2、Sybase、Informix、PostgreSQL数据库代码
- db2 数据库代码页
- oracle,mysql,sqlserver,db2取前十条记录sql语句
- 数据库Oracle DB2 MySQL 分页
- oralce与mysql中,如何删除重复记录
- [DB2 学习记录]8. 编目数据库
- db2数据库迁移到mssql记录
- db2数据库迁移到mssql记录之一
- mysql迁移到db2实践记录
- mysql数据库迁移到db2数据库
- C#连接各种数据库代码Access,Sql Server,Oracle,Mysql,IBM DB2,Sybase
- 使用delphi获得字符内码
- 正则基础之--/b单词边界
- 正整数中数字1的计数问题 - 简单算法(上)
- 关于oom kiler
- Linux内核中链表的实现
- DB2、oralce、mysql数据库取前十条记录代码
- 没野心一辈子当穷人 (转自2011-5-21 6:00:00 MSN理财综合)
- SQL语法中常用字符串函数大全
- JDBC中驱动加载的过程分析(上)
- Outlook批量转发邮件 -VBA实现
- 有效使用VC监视(Watch)工具
- oralce 基础知识
- JDBC中驱动加载的过程分析(下)
- 让百度能经常刷新网站快照的方法