以下的文章主要講述的是Oracle數據庫和DB2、MySQL取前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)Oracle數據庫與DB2、MySQL取前10條記錄的對比要取前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;
以上的相關內容就是對Oracle數據庫與DB2、MySQL取前10條記錄的對比的介紹,望你能有所收獲。