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

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

提交

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

标签：海信 2024-11-22

REDMI K80发布会邀请函出圈：5瓶辣酱背后的暖心故事

标签：红米 Redmi 小米雷军红米K80 2024-11-22

梅赛德斯-奔驰国内召回A级、E级与AMG GT部分车辆

标签：梅赛德斯-奔驰 2024-11-22

2024世界互联网大会乌镇峰会闭幕：中国已成全球最大互联网市场

标签：世界互联网大会 6G 2024-11-22

十月份国内智能门锁市场首次实现单月突破百万套销量

标签：智能门锁 2024-11-22

专题更多

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

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

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

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

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

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

REDMI K80发布会邀请函出圈：5瓶辣酱背后的暖心故事

梅赛德斯-奔驰国内召回A级、E级与AMG GT部分车辆

2024世界互联网大会乌镇峰会闭幕：中国已成全球最大互联网市场

十月份国内智能门锁市场首次实现单月突破百万套销量

REDMI K80发布会邀请函出圈：5瓶辣酱背后的暖心故事

努比亚Z70 Ultra评测：集屏幕、影像、AI优势于一身 2024最具个性化的旗舰之选

努比亚Z70 Ultra发布：第七代屏下前摄 首发星云AI 4599元起

华为折叠屏更加耐用可靠！华为Mate X6首发分布式玄武架构

红米logo换新：REDMI全部改大写 K80将率先采用

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

格力电器申请“格力女王”“蒙面女王”医疗器械类商标

2024Q3全球电视出货量达到5233万台 环比增长9.6%

预计2024年国内智能盒子销量同比去年萎缩20%

2024年电视行业用户满意度测评分数创出新高

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

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

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

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

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

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

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

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

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

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

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

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

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

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

AGI时代，AI软着陆的新范式注定属于更多的企业

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

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

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

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

芯片生产，磨难重重

梅赛德斯-奔驰国内召回A级、E级与AMG GT部分车辆

小米纯电SUV代号MX11 预计明年2-3月份上市

我国电动汽车充电桩保有量已超1188万台 同比增49.4%

乘联会预计11月新能源汽车零售128万辆 渗透率超53%

华为注册仙界、天界、君界等多个汽车类商标

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

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

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

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

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

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

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

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

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

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

专题 更多

驱动号 更多

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

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

努比亚Z70 Ultra发布：第七代屏下前摄首发星云AI 4599元起

2024Q3全球电视出货量达到5233万台环比增长9.6%

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

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

我国电动汽车充电桩保有量已超1188万台同比增49.4%

乘联会预计11月新能源汽车零售128万辆渗透率超53%

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

专题更多

驱动号更多