Oracle与DB2、MySQL取前10条记录的对比
来源:互联网 发布:淘宝店铺怎么改所在地 编辑:程序博客网 时间:2024/05/18 21: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;
(1) 取前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;
(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;
(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<=10select * 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<=10select * 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 onlyselect * 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<=10select * 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- Oracle与DB2、MySQL取前10条记录的对比
- Oracle与DB2、MySQL取前10条记录的对比
- Oracle与DB2、MySQL取前10条记录的对比
- Oracle 取前10条记录
- db2取前n条记录
- Oracle取中间记录 取前10条记录 Oracle按排序取中间记录
- Oracle,Mysql,SQL Server等数据库取前N条记录的不同写法
- Oracle,Mysql,SQL Server等数据库取前N条记录的不同写法 (有点发现)
- Oracle,Mysql,SQL Server等数据库取前N条记录的不同写法
- 取前10条记录
- oracle 取前N条记录
- mysql分组取前n条记录
- db2查询前10条记录
- mysql分组排序取前N条记录的最简洁的单条sql !
- mysql分组排序取前N条记录的最简洁的单条sql
- 随机取每天 前10条记录
- 查询前10条记录,取后面5条记录的sql代码
- mysql取前10条数的查询语句
- ORA-39006/39213错误
- ORACLE_触发器
- WinCE注册表操作API
- scrollLeft、offsetLeft、clientLeft、clientHeight详解
- CSting与char int sting的转换
- Oracle与DB2、MySQL取前10条记录的对比
- 设计模式之模板方法模式(Template)
- win7下Memcache的安装
- wget 命令下载远程ftp服务器上的文件
- Ajax实现用户名验证
- HDOJ 3472 - HS BDC 判断混合图是否存在欧拉通路
- JAVA连接各种数据库
- static>suite>test>class>method testng
- 如何使用TrackPopupMenu创建菜单