借助rownum中求Oracle表中前三名（三甲：状元榜眼探花）的方法（总计三种方法，以讲述rownum的使用为主）
要求前三名，MySQL中有order by排序，limit限制数量，结果很容易得到，而且limit的执行顺序也在order by之后，写出的sql高效易懂而不易出错。
但在oracle中，由于没有limit子句，人们喜欢求助于rownum伪列，但是，因为rownum身处select子句中，而select子句的执行顺序先于order by，因此只有order by比rownum更深一个层次，这样得到的伪列才有效，否则如果rownum和order by处于同一层次，这时的伪列是无效的！如果忘了这一点，很容易写成错误的sql语句，下面将就此举例说明：
待测试的表结构：
create table tb_student01( id number(4,0) not null primary key, name nvarchar2(20) not null, score number(3,0) not null)
充值语句（注意我故意打乱了插入顺序，就是不想在sql写错时还能因为插入顺序的关系而蒙到正确结果）：
insert into tb_student01(id,name,score) values('7','张三',77); insert into tb_student01(id,name,score) values('8','李四',88); insert into tb_student01(id,name,score) values('6','Felix',10); insert into tb_student01(id,name,score) values('10','赵六',15); insert into tb_student01(id,name,score) values('11','孙七',67); insert into tb_student01(id,name,score) values('12','钱八',37); insert into tb_student01(id,name,score) values('1','Andy',67); insert into tb_student01(id,name,score) values('2','Bill',98); insert into tb_student01(id,name,score) values('9','王五',100); insert into tb_student01(id,name,score) values('3','Cindy',25); insert into tb_student01(id,name,score) values('4','Douglas',64); insert into tb_student01(id,name,score) values('5','Eliot',99);
稍稍目测一下，100分的王五，99分的Eliot，98分的Bill将是期望的三甲。
首先我们看下面的SQL语句会是什么结果：
select rownum as rn,a.* from tb_student01 a order by a.score desc
SQL> select rownum as rn,a.* from tb_student01 a order by a.score desc; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 9 9 王五 100 12 5 Eliot 99 8 2 Bill 98 2 8 李四 88 1 7 张三 77 5 11 孙七 67 7 1 Andy 67 11 4 Douglas 64 6 12 钱八 37 10 3 Cindy 25 4 10 赵六 15 RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 3 6 Felix 10 已选择12行。 已用时间: 00: 00: 00.00
从上面我们就能看出王五的rn值不是1而是9，Eliot的rn值不是2而是12，Bill的rn值不是3而是8！
发生这种情况的原因是rownum身处的select子句执行顺序是高于order by的，这导致了排序前rownum就按初始的插入顺序被赋上了值。
再用rn值进行筛选，得到的结果让人瞠目结舌：
select b.* from (select rownum as rn,a.* from tb_student01 a order by a.score desc) b where b.rn<4
SQL> select b.* from 2 (select rownum as rn,a.* from tb_student01 a order by a.score desc) b 3 where b.rn<4; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 2 8 李四 88 1 7 张三 77 3 6 Felix 10 已用时间: 00: 00: 00.01
三甲中不仅没有90分以上的，连考10分的Felix都被放到了三甲里，学生们如果知道真实原因会写完姓名后就抢先交卷，因为越早被批改，记录就越早出现在数据库中，也就越有可能位列三甲！这是多么荒唐的事情！
为了杜绝这种荒诞，我们必须重写sql，其中要点是先按分数排序再附上伪列。
首先排序：
select a.* from tb_student01 a order by a.score desc
SQL> select a.* from tb_student01 a order by a.score desc; ID NAME SCORE ---------- ---------------------------------------- ---------- 9 王五 100 5 Eliot 99 2 Bill 98 8 李四 88 7 张三 77 11 孙七 67 1 Andy 67 4 Douglas 64 12 钱八 37 3 Cindy 25 10 赵六 15 ID NAME SCORE ---------- ---------------------------------------- ---------- 6 Felix 10 已选择12行。 已用时间: 00: 00: 00.01
其次，再附上伪列：
SQL> select rownum as rn,b.* from 2 (select a.* from tb_student01 a order by a.score desc) b ; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 1 9 王五 100 2 5 Eliot 99 3 2 Bill 98 4 8 李四 88 5 7 张三 77 6 11 孙七 67 7 1 Andy 67 8 4 Douglas 64 9 12 钱八 37 10 3 Cindy 25 11 10 赵六 15 RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 12 6 Felix 10 已选择12行。 已用时间: 00: 00: 00.01
最后，对rn值进行筛选就得到最终结果了：
SQL> select c.* from 2 ( select rownum as rn,b.* from 3 (select a.* from tb_student01 a order by a.score desc) b ) c 4 where c.rn<4; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 1 9 王五 100 2 5 Eliot 99 3 2 Bill 98 已用时间: 00: 00: 00.01
这个结果和预期值是一致的，所以最终查询三甲的SQL是：
select c.* from ( select rownum as rn,b.* from (select a.* from tb_student01 a order by a.score desc) b ) c where c.rn<4
当然，不借助rownum也可以得到正确结果，比如说分析函数rank（）：
SQL：
select b.* from (select rank() over (order by score desc) as rn,a.* from tb_student01 a ) b where b.rn<4
执行结果：
SQL> select b.* from 2 (select rank() over (order by score desc) as rn,a.* from tb_student01 a ) b 3 where b.rn<4; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 1 9 王五 100 2 5 Eliot 99 3 2 Bill 98 已用时间: 00: 00: 00.00
或是借助row_number函数
SQL：
select b.* from (select row_number() over (order by score desc) as rn,a.* from tb_student01 a ) b where b.rn<4
执行结果：
SQL> select b.* from 2 (select row_number() over (order by score desc) as rn,a.* from tb_student01 a ) b 3 where b.rn<4; RN ID NAME SCORE ---------- ---------- ---------------------------------------- ---------- 1 9 王五 100 2 5 Eliot 99 3 2 Bill 98 已用时间: 00: 00: 00.00
--2020年3月28日--
以上用到的全部SQL：
create table tb_student01( id number(4,0) not null primary key, name nvarchar2(20) not null, score number(3,0) not null) insert into tb_student01(id,name,score) values('7','张三',77); insert into tb_student01(id,name,score) values('8','李四',88); insert into tb_student01(id,name,score) values('6','Felix',10); insert into tb_student01(id,name,score) values('10','赵六',15); insert into tb_student01(id,name,score) values('11','孙七',67); insert into tb_student01(id,name,score) values('12','钱八',37); insert into tb_student01(id,name,score) values('1','Andy',67); insert into tb_student01(id,name,score) values('2','Bill',98); insert into tb_student01(id,name,score) values('9','王五',100); insert into tb_student01(id,name,score) values('3','Cindy',25); insert into tb_student01(id,name,score) values('4','Douglas',64); insert into tb_student01(id,name,score) values('5','Eliot',99); Wrong： select rownum as rn,a.* from tb_student01 a order by a.score desc select b.* from (select rownum as rn,a.* from tb_student01 a order by a.score desc) b where b.rn<4 Correct: select a.* from tb_student01 a order by a.score desc select rownum as rn,b.* from (select a.* from tb_student01 a order by a.score desc) b select c.* from ( select rownum as rn,b.* from (select a.* from tb_student01 a order by a.score desc) b ) c where c.rn<4 select rank() over (order by score desc) as rn,a.* from tb_student01 a select b.* from (select rank() over (order by score desc) as rn,a.* from tb_student01 a ) b where b.rn<4 或是row_number函数 select b.* from (select row_number() over (order by score desc) as rn,a.* from tb_student01 a ) b where b.rn<4
分类:
Oracle.简易Sql文
, Oracle.语法
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
2017-12-31 x为正变数，求y=x^3/(x^4+4)的最大值
2017-12-31 【转载】《妻妾成群》：一切执迷，皆为牢笼
2014-12-31 爪哇国新游记之三十三----目录文件操作
2014-12-31 一个类似股票看板的自刷新页面的制作