§ Oracle兼容-语法-ROWNUM
§ 1.语法
ROWNUM是一个伪列，主要用来实现分布功能，类似于LIMIT 用法。使用方式如下：
greatsql> SELECT * FROM employees WHERE ROWNUM < 11;
greatsql> SELECT * FROM (SELECT ROWNUM rn, x FROM t1) t1 WHERE t1.rn BETWEEN 3 AND 5;
1
2
3
2
3
§ 2.语义描述
greatsql> SELECT * FROM employees WHERE ROWNUM > 1;
1
解释：对获取到的第一行记录，其 rownum 编号为1， 而 where rownum > 1 不满足条件（1>1 为 false），跳过；第二行记录，它的编号仍然为1，还是不满足条件（rownum >1 为false），所以该查询结果永远为空。
greatsql> SELECT * FROM t1;
+------+------+
| ida | age |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 0 |
+------+------+
greatsql> SELECT rownum, ida FROM t1 ORDER BY age;
+--------+------+
| rownum | ida |
+--------+------+
| 3 | 3 |
| 1 | 1 |
| 2 | 2 |
+--------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
解释：因为 ORDER BY 操作是在最后步骤完成的，此时可能会对 rownum 的最终显示顺序有影响。
§ 2.1 对rownum值的限制
假定WHERE条件中，rownum 设定的值为N，则对N的要求是：
- 条件值N必须是大于等于1的正数，否则查询结果是空集。
- 条件
rownum > N返回结果是空集。因为对于取到的第一行记录，rownum编号为1， 不符合条件（rownum > N），则该行被丢弃。第二行记录，依然编号为1，也不满足， 依次类推，结果集为空。上面的例子已有演示。 - 当条件为
rownum <= 1时，只可以返回第一行记录。 - 当条件为
rownum >= 1时，会返回所有记录。
§ 2.2 执行顺序说明
greatsql> SELECT * FROM t1 WHERE c1=? AND c2=? AND rownum <= N;
1
上述SQL语句中包含多个过滤条件，rownum 过滤条件会放在最后判断执行。
§ 3. 其他使用限制说明
§ 3.1 对JOIN的支持不同
在外连接语法中，不支持 rownum 作为 join key 使用，而内连接可以。因为在外连接中使用 rownum 会导致语义不明确。
greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.ida = rownum;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'ROWNUM occur in outer JOIN ON conditions.'
greatsql> SELECT * FROM t1 JOIN t2 ON t1.ida = rownum;
+------+------+----+------------+-------+---------------------+
| ida | age | id | name | other | created |
+------+------+----+------------+-------+---------------------+
| 1 | 2 | 5 | Fifth Name | 55555 | 2004-04-04 04:04:04 |
| 2 | 3 | 5 | Fifth Name | 55555 | 2004-04-04 04:04:04 |
| 3 | 0 | 5 | Fifth Name | 55555 | 2004-04-04 04:04:04 |
+------+------+----+------------+-------+---------------------+
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
§ 3.2 对子查询的限制
- 支持
FROM后子查询使用rownum。 - 支持非相关标量子查询中使用
rownum。对in/any/all/some子查询， 无论相关非相关都不支持使用rownum，主要原因是在nested loop JOIN的时候，存在执行顺序问题，可能导致结果不一样。这时候报错如下：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'ROWNUM & IN/ALL/ANY/SOME subquery';
1
§ 3.3 查询结果与Oracle可能不一样
因数据输出顺序不一致，在此基础上做
rownum过滤，会导致最终显示结果可能也不一样。优化行为不一致，可能导致输出结果不同。例如下面几个案例：
-- 下面这个SQL，GreatSQL和Oracle的结果一样
greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.r1=t2.r1;
-- 下面这个SQL，GreatSQL按照原语句，对JOIN结果集作了rownum<t1.r1 过滤
greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.r1=t2.r1 WHERE rownum < t1.r1;
-- 下面这个SQL，Oracle按照 JOIN key进行了过滤的推导，推导出一个rownum < t2.r1的过滤，这样，就将最终结果集中t2表产生的null行都过滤掉了
-- 这并不符合原始语义
greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.r1=t2.r1 WHERE rownum < t1.r1;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
§ 问题反馈
§ 联系我们
扫码关注微信公众号
