首页新闻手机家电数码电脑财经大模型直播

Oracle与DB2、MySQL取前10条记录的对比

来源：互联网作者：若水 2008-03-19/13:44

软件频道

编程学院

IBM DB2

入门教程

正文

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<=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

{{cmoun}}人已赞

评论 {{userinfo.comments}}

{{c.nickname}}
{{c.create_time}}

{{c.content}}

点赞已赞 ({{c.count_praise}}) 回复({{c.count_reply}})

{{child.nickname}}
{{child.create_time}}

{{child.content}}

查看全部评论

{{money}}元

A {{question.A}}

B {{question.B}}

C {{question.C}}

D {{question.D}}

提交

K100 Pro评测:185Hz、8580mAh、骁龙8E5V Series，同档全面领先的均衡之作

标签： REDMI K100 Pro 2026-08-11

新深蓝S05上市11.59万元起，全球时尚激光智能SUV全面进阶

标签：新深蓝S05 2026-08-06

狂言背后的现实博弈：拆解蔚来汽车李斌三句争议言论

标签： 2026-08-04

路虎加速电动化：越野豪门对阵国产新势力，优劣势全解析

标签：路虎卫士 2026-07-31

平地干拔阴山“英雄坡”！岚图发布中国首款全地形智能底盘“虎踞”

标签：虎踞” 岚图泰山 2026-07-30

专题更多

2024年度中兴通讯云网生态峰会

聚焦315提振消费信心专题报道

Oracle与DB2、MySQL取前10条记录的对比

{{c.nickname}} {{c.create_time}}

{{child.nickname}} {{child.create_time}}

K100 Pro评测:185Hz、8580mAh、骁龙8E5V Series，同档全面领先的均衡之作

新深蓝S05上市11.59万元起，全球时尚激光智能SUV全面进阶

狂言背后的现实博弈：拆解蔚来汽车李斌三句争议言论

路虎加速电动化：越野豪门对阵国产新势力，优劣势全解析

平地干拔阴山“英雄坡”！ 岚图发布中国首款全地形智能底盘“虎踞”

K100 Pro评测:185Hz、8580mAh、骁龙8E5V Series，同档全面领先的均衡之作

REDMI Note 17 Pro评测：同档最强抗摔防水，2026年千元机市场的品质守门员

11000mAh电池+10000nits高亮屏，“耐用神机”荣耀X80 Pro Max发布

旗舰性能 直上青云！iQOO15T 3799元起正式发布

苹果首款折叠屏iPhone曝光：仅售两款低调配色，定价或14999元起

TCL发布Q系列旗舰回音壁：打破海外垄断，高端影音迎来中国声音

618扫地机市场再升温：石头P20 Max把技术做进真实家务里

国产电视彻底主导国内市场，三星索尼等外资品牌份额跌破3%

2026洗地机选购终极答案：为什么说石头是首选？

TCL发布会解析：Q9M Pro领衔，T7M系列双星登场，163吋Micro LED双曜压轴

双积分双榜单登顶！比亚迪以长期主义领跑汽车产业低碳转型

双11华为笔记本怎么买？一篇看懂各款特点与超值优惠

性能、续航、AI都有料！Hi MateBook D 16真实使用体验分享

荣耀新突破：挑战极限！即将发布重量不足980克的超级轻薄笔记本

苹果Vision Pro国行版开启预购，1TB版售价32999起

抖音上线电影评分功能 多部热映影片均低于及格线

黄仁勋央视猛夸华为、小米、DeepSeek等：我不是尬吹 中国是真强

直播电商：抖音、快手、视频号上演“刀光剑影”

小熊电器一季报业绩下滑：短期承压明显，销售费用再创新高

第四范式亏损有所收窄：短期股价大跌，客户高黏性被机构看好

埃夫特 · 启智亮相WAIC | 首发HumanGPT，以通用技术底座破解行业"共创难"

微软拟为Copilot“换芯”Kimi K3：年省6亿美元背后的商业账与政治险

月之暗面拟启动IPO前最后一轮融资，目标投前估值500亿美元

阿里Token Plan个人版正式亮相，2.4T参数旗舰模型Qwen3.8-Max抢先体验

月之暗面Kimi因算力紧缺暂停C端新用户订阅，全力保障现有用户体验

奥运观赛AI新体验！通义App上线“赛事百事通”等多款新功能

云计算首次超越卫星！超三分之二奥运直播信号基于阿里云向全球分发

国际奥委会主席巴赫：阿里AI技术将巴黎奥运转播带到新高度

“崩”了的阿里云，能靠AI带动增长？

芯片生产，磨难重重

硬核解读：岚图追光S完成360度隧道大回环挑战，到底有多大含金量？

比亚迪秦MAX正式上市 售价9.99-14.39万元

年轻人首选B级闪充轿车秦MAX正式上市，售价10.99-14.39万元

超越奔驰！ 岚图追光S 完成全球首个量产车型360°隧道大回环

豹启新境，2027款海豹06满配上市，售价9.99万元起

云计算迎变局：阿里云、腾讯云“各有千秋”

Sora正在颠覆游戏行业，CEO们该怎么应对？

处置1.9万个账号和240万条视频，抖音治理不实信息这一年

道总有理：苹果其实不想成为全球第一

抛开大模型故事，阿里、抖音、京东都在攻坚AI电商哪个方向？

西北工业大学研究团队模仿萤火虫通信机制 实现无人机光链路协同飞行

价格才不是小米汽车的最大“杀器”

懂车帝与车企争论的背后，新能源测试标准何时迎来终局？

惨！判赔10亿巨款，阿里卸下包袱重新出发

立讯收购Qorvo中国工厂的幕后推手

专题 更多

驱动号 更多

{{c.nickname}}
{{c.create_time}}

{{child.nickname}}
{{child.create_time}}

平地干拔阴山“英雄坡”！岚图发布中国首款全地形智能底盘“虎踞”

旗舰性能直上青云！iQOO15T 3799元起正式发布

抖音上线电影评分功能多部热映影片均低于及格线

黄仁勋央视猛夸华为、小米、DeepSeek等：我不是尬吹中国是真强

比亚迪秦MAX正式上市售价9.99-14.39万元

超越奔驰！岚图追光S 完成全球首个量产车型360°隧道大回环

西北工业大学研究团队模仿萤火虫通信机制实现无人机光链路协同飞行

专题更多

驱动号更多