更新时间:2024-05-07 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子句更新。
  • SELECT ... FOR UPDATE语句不支持ROWNUM用作投影列和WHERE条件。
  • 若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
    16
    17
    18
    19
    gaussdb=# CREATE TABLE test (a int, b int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    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=3.45..3.49 rows=3 width=4)
       Group By Key: a, ROWNUM
       Filter: ((ROWNUM) < 5::numeric)
       ->  Rownum  (cost=0.19..3.40 rows=10 width=4)
             ->  Streaming (type: GATHER)  (cost=0.19..3.40 rows=10 width=4)
                   Node/s: All datanodes
                   ->  Seq Scan on test  (cost=0.00..3.03 rows=10 width=4)
    (7 rows)                     
    
  • 子查询中如果存在ROWNUM条件则不允许谓词下推至扫描节点:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    gaussdb=# EXPLAIN SELECT * FROM (SELECT * FROM test WHERE rownum < 5) WHERE b < 5; -- b<5 不能下推至seqscan
                                        QUERY PLAN                                     
    -----------------------------------------------------------------------------------
     Streaming (type: GATHER)  (cost=0.06..0.76 rows=2 width=8)
       Node/s: All datanodes
       ->  Subquery Scan on __unnamed_subquery__  (cost=0.00..0.63 rows=2 width=8)
             Filter: (__unnamed_subquery__.b < 5)
             ->  Rownum  (cost=0.00..0.57 rows=5 width=8)
                   StopKey: (ROWNUM < 5::numeric)
                   ->  Streaming(type: BROADCAST)  (cost=0.00..1.71 rows=15 width=8)
                         Spawn on: All datanodes
                         ->  Rownum  (cost=0.00..1.68 rows=5 width=8)
                               StopKey: (ROWNUM < 5::numeric)
                               ->  Seq Scan on test  (cost=0.00..3.03 rows=10 width=8)
    (11 rows)
                
    gaussdb=# DROP TABLE test;
    DROP TABLE
    
  • ROWNUM查询结果将会由于CN接收DN数据的顺序不同导致查询结果不稳定。
     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
    gaussdb=# CREATE TABLE test(a int, b int);
    gaussdb=# INSERT INTO test VALUES(generate_series(1,10),generate_series(1,10));
    
    --输出表Students前10行数据。
    gaussdb=# SELECT rownum,* FROM test; 
     rownum | a  | b  
    --------+----+----
          1 |  1 |  1
          2 |  2 |  2
          3 |  5 |  5
          4 |  6 |  6
          5 |  8 |  8
          6 |  9 |  9
          7 |  3 |  3
          8 |  4 |  4
          9 |  7 |  7
         10 | 10 | 10
    (10 rows)
    
    --CN先收到DN2数据再收到DN1数据。
    gaussdb=# SELECT rownum,* FROM test; 
     rownum | a  | b  
    --------+----+----
          1 |  3 |  3
          2 |  4 |  4
          3 |  7 |  7
          4 | 10 | 10
          5 |  1 |  1
          6 |  2 |  2
          7 |  5 |  5
          8 |  6 |  6
          9 |  8 |  8
         10 |  9 |  9
    (10 rows)
    
  • 不推荐ROWNUM条件用于JOIN ON子句,GaussDB中ROWNUM条件用于JOIN ON子句时在LEFT JOIN、RIGHT JOIN、FULL JOIN场景下和MERGE INTO场景下与其他数据库行为不一致,直接进行业务迁移存在风险。