建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
Oracle數(shù)據(jù)庫(kù)和DB2取前10條記錄的實(shí)際對(duì)比
以下的文章主要講述的是Oracle數(shù)據(jù)庫(kù)和DB2、MySQL取前10條記錄的實(shí)際對(duì)比，以下的文章主要以O(shè)ralce為示例來講解它們的不同，以下就是文章的詳細(xì)內(nèi)容介紹，希望大家能有所收獲。
成都創(chuàng)新互聯(lián)主營(yíng)肥西網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,重慶APP開發(fā),肥西h5重慶小程序開發(fā)搭建,肥西網(wǎng)站營(yíng)銷推廣歡迎肥西等地區(qū)企業(yè)咨詢
1. ***選擇：利用分析函數(shù)
- 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;
注釋：使用序列時(shí)不能基于整個(gè)記錄集合來進(jìn)行排序,假如指定了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)Oracle數(shù)據(jù)庫(kù)與DB2、MySQL取前10條記錄的對(duì)比要取前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;
以上的相關(guān)內(nèi)容就是對(duì)Oracle數(shù)據(jù)庫(kù)與DB2、MySQL取前10條記錄的對(duì)比的介紹，望你能有所收獲。
文章出自:http://www.programbbs.com/doc/class10-1.htm
【編輯推薦】
- 如何對(duì)Oracle sql 進(jìn)行性能優(yōu)化的調(diào)整
- Oracle參數(shù)及參數(shù)文件7大類解析
- 6種找回Oracle數(shù)據(jù)庫(kù)初始密碼的方法
- Oracle控制文件如何高效管理
- OraclePL編程語言的優(yōu)點(diǎn)介紹
當(dāng)前名稱：Oracle數(shù)據(jù)庫(kù)和DB2取前10條記錄的實(shí)際對(duì)比
本文地址：http://www.dlmjj.cn/article/dppccoe.html
