共计 4584 个字符，预计需要花费 12 分钟才能阅读完成。
关于 Oracle 中 rownum 使用总结
对于 Oracle 的 rownum 问题，很多资料都说不支持 >,>=,=,between…and，只能用以上符号(<、<=、!=)，并非说用 >,& gt;=,=,between..and 时会提示 SQL 语法错误，而是经常是查不出一条记录来，还会出现似乎是莫名其妙的结果来，其实您只要理解好了这个 rownum 伪列的意义就不应该感到惊奇，同样是伪列，rownum 与 rowid 可有些不一样，下面以例子说明
假设某个表 t1(c1) 有 20 条记录
如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于号，查出来的结果很容易地与一般理解在概念上能达成一致，应该不会有任何疑问的。
可如果用 select rownum,c1 from t1 where rownum > 10 (如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面 10 条记录)，你就会发现，显示出来的结果要让您失望了，也许您还会怀疑是不谁删了一 些记录，然后查看记录数，仍然是 20 条啊？那问题是出在哪呢？
先好好理解 rownum 的意义吧。因为 ROWNUM 是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从 1 开始排起的。所以你选出的结果不可能没有 1，而有其他大于 1 的值。所以您没办法期望得到下面的结果集：
11 aaaaaaaa
12 bbbbbbb
13 ccccccc
……………..
rownum >10 没有记录，因为第一条不满足去掉的话，第二条的 ROWNUM 又成了 1，所以永远没有满足条件的记录。或者可以这样理解：
ROWNUM 是一个序列，是 oracle 数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则 rownum 值为 1，第二条为 2，依次类 推。如果你用 >,>=,=,between…and 这些条件，因为从缓冲区或数据文件中得到的第一条记录的 rownum 为 1，则被删除，接着取下条，可是它的 rownum 还是 1，又被删除，依次类推，便没有了数据。
有了以上从不同方面建立起来的对 rownum 的概念，那我们可以来认识使用 rownum 的几种现像
1. select rownum,c1 from t1 where rownum != 10 为何是返回前 9 条数据呢？它与 select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢？
因为是在查询到结果集后，显示完第 9 条记录后，之后的记录也都是 != 10, 或者 > =10, 所以只显示前面 9 条记录。也可以这样理解，rownum 为 9 后的记录的 rownum 为 10，因条件为 !=10，所以去掉，其后记录补上，rownum 又是 10，也去掉，如果下去也就只会显示前面 9 条记录了
2. 为什么 rownum >1 时查不到一条记录，而 rownum >0 或 rownum >=1 却总显示所以的记录
因为 rownum 是在查询到的结果集后加上去的，它总是从 1 开始
3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用 between 2 and 10 却得不到结果
原因同上一样，因为 rownum 总是从 1 开始
从上可以看出，任何时候想把 rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了 rownum=1 就像空中楼阁一般不能存在，所以你的 rownum 条件要包含到 1
但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句, 把 rownum 先生成，然后对他进行查询。
select *
from (selet rownum as rn，t1.* from a where …)
where rn >10
一般代码中对结果集进行分页就是这么干的。
另外：rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置 ID，在 DB 中唯一。只要记录没被搬动过，rowid 是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum 那些情况发生。
另外还要注意：rownum 不能以任何基表的名称作为前缀。
MySQL 中的 rownum 的实现
MySQL 几乎模拟了 Oracle,SQL Server 等商业数据库的大部分功能，函数。但很可惜，到目前的版本 (5.1.33) 为止, 仍没有实现 ROWNUM 这个功能。
下面介绍几种具体的实现方法.
建立实验环境如下
MySQL> create table tbl (
-> id int primary key,
-> col int
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into tbl values
-> (1,26),
-> (2,46),
-> (3,35),
-> (4,68),
-> (5,93),
-> (6,92);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from tbl order by col;
+—-+——+
| id | col |
+—-+——+
| 1 | 26 |
| 3 | 35 |
| 2 | 46 |
| 4 | 68 |
| 6 | 92 |
| 5 | 93 |
+—-+——+
6 rows in set (0.00 sec)
1. 直接在程序中实现；
这应该算是效率最高的一种，也极为方便。直接在你的开发程序中（PHP/ASP/C/…）等中，直接初始化一个变量 nRowNum=0，然后在 while 记录集时，nRowNum++; 然后输出即可。
2. 使用 MySQL 变量；在某些情况下，无法通过修改程序来实现时，可以考虑这种方法。
缺点，@x 变量是 connection 级的，再次查询的时候需要初始化。一般来说 PHP 等 B / S 应用没有这个问题。但 C / S 如果 connection 一只保持则要考虑 set @x=0
mysql> select @x:=ifnull(@x,0)+1 as rownum,id,col
-> from tbl
-> order by col;
+——–+—-+——+
| rownum | id | col |
+——–+—-+——+
| 1 | 1 | 26 |
| 1 | 3 | 35 |
| 1 | 2 | 46 |
| 1 | 4 | 68 |
| 1 | 6 | 92 |
| 1 | 5 | 93 |
+——–+—-+——+
6 rows in set (0.00 sec)
3. 使用联接查询（笛卡尔积）
缺点，显然效率会差一些。
利用表的自联接，代码如下，你可以直接试一下 select a.*,b.* from tbl a,tbl b where a.col>=b.col 以理解这个方法原理。
mysql> select a.id,a.col,count(*) as rownum
-> from tbl a,tbl b
-> where a.col>=b.col
-> group by a.id,a.col;
+—-+——+——–+
| id | col | rownum |
+—-+——+——–+
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+—-+——+——–+
6 rows in set (0.00 sec)
4. 子查询
缺点，和联接查询一样，具体的效率要看索引的配置和 MySQL 的优化结果。
mysql> select a.*,
-> (select count(*) from tbl where col<=a.col) as rownum
-> from tbl a;
+—-+——+——–+
| id | col | rownum |
+—-+——+——–+
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+—-+——+——–+
6 rows in set (0.06 sec)
4. 设定变量
MySQL 如何实现 Oracle 的 ROWNUM2010/01/28 23:56mysql> SELECT * FROM frutas;
+———–+———-+
| nombre | color |
+———–+———-+
| fresa | rojo |
| platano | amarillo |
| manzana | verde |
| uva | verde |
| pera | verde |
| mandarina | naranja |
| melocoton | marron |
| limon | amarillo |
+———–+———-+
8 rows in set (0,00 sec)
【一条 sql 语句完成，不 set 变量】
SELECT @rownum:=@rownum+1 AS rownum, frutas.*
FROM (SELECT @rownum:=0) r, frutas;
mysql> SELECT @rownum:=@rownum+1 AS rownum, frutas.*
-> FROM (SELECT @rownum:=0) r, frutas;
+——–+———–+———-+
| rownum | nombre | color |
+——–+———–+———-+
| 1 | fresa | rojo |
| 2 | platano | amarillo |
| 3 | manzana | verde |
| 4 | uva | verde |
| 5 | pera | verde |
| 6 | mandarina | naranja |
| 7 | melocoton | marron |
| 8 | limon | amarillo |
+——–+———–+———-+
8 rows in set (0,00 sec)
但是如何把这个查询放在 create view 里面
mysql> CREATE VIEW vw_frutas AS SELECT @rownum:=@rownum+1 AS rownum, frutas.*
-> FROM (SELECT @rownum:=0) r, frutas;
ERROR 1351 (HY000): View’s SELECT contains a variable or parameter
做为一款开源的数据库系统，MySQL 无疑是一个不做的产品。它的更新速度，文档维护都不逊于几大商业数据库产品。估计在下一个版本中，我们可以��到由 MySQL 自身实现的 ROWNUM。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-10/136034.htm
