更新时间：2024-04-25 GMT+08:00
伪列
ROWNUM是一个伪列，它返回一个数字，表示从查询中获取结果的行编号。第一行的ROWNUM为1，第二行的为2，依此类推。
使用ROWNUM来限制查询返回的行数，如以下示例所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
gaussdb=# CREATE TABLE Students (name varchar(20), id int) with (STORAGE_TYPE = USTORE); gaussdb=# INSERT INTO Students VALUES ('Jack', 35); gaussdb=# INSERT INTO Students VALUES ('Leon', 15); gaussdb=# INSERT INTO Students VALUES ('James', 24); gaussdb=# INSERT INTO Students VALUES ('Taker', 81); gaussdb=# INSERT INTO Students VALUES ('Mary', 25); gaussdb=# INSERT INTO Students VALUES ('Rose', 64); gaussdb=# INSERT INTO Students VALUES ('Perl', 18); gaussdb=# INSERT INTO Students VALUES ('Under', 57); gaussdb=# INSERT INTO Students VALUES ('Angel', 101); gaussdb=# INSERT INTO Students VALUES ('Frank', 20); gaussdb=# INSERT INTO Students VALUES ('Charlie', 40); --输出表Students前10行数据。 gaussdb=# SELECT * FROM Students WHERE rownum <= 10; name | id -------+----- Jack | 35 Leon | 15 James | 24 Taker | 81 Mary | 25 Rose | 64 Perl | 18 Under | 57 Angel | 101 Frank | 20 (10 rows) |
如果有子句跟在同一查询语句中，则结果输出的行将按照子句重新排序:
1 2 3 4 5 6 7 8 |
gaussdb=# SELECT * FROM Students WHERE rownum < 5 order by 1; name | id -------+---- Jack | 35 James | 24 Leon | 15 Taker | 81 (4 rows) |
如果将子句嵌入到子查询中并将条件放在顶级的查询中，则能够在排序后使用ROWNUM条件:
1 2 3 4 5 6 |
gaussdb=# SELECT rownum, * FROM (SELECT * FROM Students order by 1) WHERE rownum <= 2; rownum | name | id --------+---------+----- 1 | Angel | 101 2 | Charlie | 40 (2 rows) |
当ROWNUM大于正整数的值，认为条件始终为 false。例如以下所示，该语句不会返回表中任何结果：
1 2 3 4 |
gaussdb=# SELECT * FROM Students WHERE rownum > 1; name | id ------+---- (0 rows) |
使用ROWNUM指定给表的一定范围的每一行分配值:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
gaussdb=# SELECT * FROM Students; name | id ---------+----- Jack | 35 Leon | 15 James | 24 Taker | 81 Mary | 25 Rose | 64 Perl | 18 Under | 57 Angel | 101 Frank | 20 Charlie | 40 (11 rows) gaussdb=# update Students set id = id + 5 WHERE rownum < 4; UPDATE 3 gaussdb=# SELECT * FROM Students; name | id ---------+----- Jack | 40 Leon | 20 James | 29 Taker | 81 Mary | 25 Rose | 64 Perl | 18 Under | 57 Angel | 101 Frank | 20 Charlie | 40 (11 rows) gaussdb=# DROP TABLE Students; DROP TABLE |
使用ROWNUM有一定的约束条件：
- ROWNUM不可作为别名，以免SQL语句出现歧义。
- 创建索引时不可使用ROWNUM。
- 创建表时默认值不可为ROWNUM。
- Where子句中不可使用ROWNUM的别名。
- 在插入数据时不可使用ROWNUM。
- 在无表查询中不可以使用ROWNUM。
- ROWNUM不能用于Limit子句。
- ROWNUM不能用于EXECUTE语句的参数。
- UPSERT语句不支持ROWNUM用做update子句更新。
- 若having子句中含有ROWNUM（且不在聚合函数中）时，group by子句中必须含有ROWNUM（且不在聚合函数中），除非group by子句存在表达式，例如：SELECT a + a FROM t group by a + a having rownum < 5。
- having子句中如果存在ROWNUM条件则不允许having子句下推至扫描节点：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
gaussdb=# CREATE TABLE test (a int, b int); CREATE TABLE gaussdb=# INSERT INTO test SELECT generate_series, generate_series from generate_series(1, 10); INSERT 0 10 --rownum条件不能下推至seqscan。 gaussdb=# EXPLAIN SELECT a,rownum FROM test group by a,rownum having rownum < 5; QUERY PLAN -------------------------------------------------------------------- HashAggregate (cost=42.23..69.10 rows=2149 width=4) Group By Key: a, ROWNUM Filter: ((ROWNUM) < 5) -> Rownum (cost=0.00..31.49 rows=2149 width=4) -> Seq Scan on test (cost=0.00..31.49 rows=2149 width=4) (5 rows)
- 子查询中如果存在ROWNUM条件则不允许谓词下推至扫描节点：
1 2 3 4 5 6 7 8 9 10 11 12 13
--b<5 不能下推至seqscan。 gaussdb=# EXPLAIN SELECT * FROM (SELECT * FROM test WHERE rownum < 5) WHERE b < 5; QUERY PLAN ------------------------------------------------------------------------- Subquery Scan on __unnamed_subquery__ (cost=0.00..0.01 rows=1 width=8) Filter: (__unnamed_subquery__.b < 5) -> Rownum (cost=0.00..0.00 rows=1 width=8) StopKey: (ROWNUM < 5) -> Seq Scan on test (cost=0.00..31.49 rows=2149 width=8) (5 rows) gaussdb=# DROP TABLE test; DROP TABLE
不推荐ROWNUM条件用于JOIN ON子句，GaussDB中ROWNUM条件用于JOIN ON子句时在LEFT JOIN、RIGHT JOIN、FULL JOIN场景下和MERGE INTO场景下与其他数据库行为不一致，直接进行业务迁移存在风险。
父主题： SQL参考
