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

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

提交

荣耀Magic7系列全面升级大王影像，首发AI超级长焦拍远更清晰

标签：荣耀 2024-12-23

售价999，打卡返全款购机券！闪极AI「拍拍镜」震撼发布

标签：闪极科技拍拍镜 2024-12-23

安全可靠精准高效讯飞AI录音笔S8离线版体验

标签：科大讯飞录音笔 2024-12-23

昨夜今晨：传ChatGPT5研发受阻 ARM与高通纠纷案判决出炉

标签：高通 ARM ChatGPT 华硕 360 OPPO 2024-12-23

比亚迪技术鱼池持续高产整车智能重新定义智能化下半场

标签：比亚迪整车智能智能化 2024-12-20

专题更多

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

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

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

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

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

荣耀Magic7系列全面升级大王影像，首发AI超级长焦拍远更清晰

售价999，打卡返全款购机券！闪极AI「拍拍镜」震撼发布

安全可靠 精准高效 讯飞AI录音笔S8离线版体验

昨夜今晨：传ChatGPT5研发受阻 ARM与高通纠纷案判决出炉

比亚迪技术鱼池持续高产 整车智能重新定义智能化下半场

荣耀Magic7系列全面升级大王影像，首发AI超级长焦拍远更清晰

重塑互联网手机市场格局 荣耀GT系列首款新品正式发布

AI学习平板新标杆，荣耀平板V9正式发布，首销优惠价1999元起

华为Mate 70 Pro+引国外机构侧目 麒麟9020芯片遭拆解分析

OPPO推出自研芯片级游戏技术“风驰游戏内核”

再造Mini LED技术天花板！TCL发布万象分区等重磅新技术

不断刷新满配清洁力上限 追觅科技重磅推出H40 Ultra洗地机

石头科技“黑五”海外市场捷报，全面跨越创历史新高！

热“雪”正当虹，长虹新款AI TV闪耀冰雪舞台

海信家电”换帅 “ 高玉玲接替代慧忠出任新任董事长

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

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

618智能穿戴怎么选？华为智能穿戴全系列选购攻略出炉

苹果新款iPad Pro遭遇渲染问题，修复补丁即将推出

业绩最新出炉，全球TOP10芯片巨头Q1表现如何？

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

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

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

不卷低价卷“性价比”：京东采销的竞争谋局

百度开发者大会即将举行，小度将发布全球首个AI原生操作系统DuerOS X

售价999，打卡返全款购机券！闪极AI「拍拍镜」震撼发布

讯飞星火4.0 Turbo正式发布，重磅首发11项技术及应用，大模型迎来规模化应用落地时代

创意无限，AI有界 七彩虹、NVIDIA和火星时代创作挑战赛，颠覆你的想象

以AI为核心的新一代架构 萤石蓝海大模型正式发布

阿里云通义千问Qwen2大型模型正式发布同步开源

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

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

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

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

芯片生产，磨难重重

比亚迪技术鱼池持续高产 整车智能重新定义智能化下半场

哪吒汽车欠款欠薪成常态 总部遭供应商上门维权

极越CEO夏一平遭员工围堵讨要说法 称担心其“跑路”

极越汽车困境波及车联网服务 传1.2万车主面临车机功能失效风险

比亚迪起诉博主“徐里里 Xll”侵权 索赔500万元

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

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

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

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

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

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

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

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

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

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

专题 更多

驱动号 更多

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

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

安全可靠精准高效讯飞AI录音笔S8离线版体验

比亚迪技术鱼池持续高产整车智能重新定义智能化下半场

重塑互联网手机市场格局荣耀GT系列首款新品正式发布

华为Mate 70 Pro+引国外机构侧目麒麟9020芯片遭拆解分析

不断刷新满配清洁力上限追觅科技重磅推出H40 Ultra洗地机

创意无限，AI有界七彩虹、NVIDIA和火星时代创作挑战赛，颠覆你的想象

以AI为核心的新一代架构萤石蓝海大模型正式发布

比亚迪技术鱼池持续高产整车智能重新定义智能化下半场

哪吒汽车欠款欠薪成常态总部遭供应商上门维权

极越CEO夏一平遭员工围堵讨要说法称担心其“跑路”

极越汽车困境波及车联网服务传1.2万车主面临车机功能失效风险

比亚迪起诉博主“徐里里 Xll”侵权索赔500万元

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

专题更多

驱动号更多