南大通用GBase 8a 支持CTE 功能with as 介绍

GBase 8a数据库集群在新版本里支持了CTE(Common Table Expression)功能，支持的版本可以查看到_t_gcluster_support_cte 这个参数。通过with as 的CTE功能，可以极大简化嵌套SQL的复杂性，提升SQL的可读性。如果没有打开会报This version of GBase doesn't yet support 'CTE' 报错。

参数

_t_gcluster_support_cte 
默认值为0

说明

如果用户使用这个功能，需要打开这个参数，支持session和global方式。 具体参数设置细节介绍，请参考 GBase 8a数据库设置参数的方法

样例

如下演示session级的设置和使用样例。可以看到如果没有打开参数，使用with as 会报错 。

ERROR 1235 (42000): This version of GBase doesn't yet support 'CTE'
gbase> show variables like '%CTE%';
+--------------------------+--------------------------------------------------+
| Variable_name            | Value                                            |
+--------------------------+--------------------------------------------------+
| _t_gcluster_support_cte  | 0                                                |
| character_set_client     | utf8                                             |
| character_set_connection | utf8                                             |
| character_set_database   | utf8                                             |
| character_set_filesystem | binary                                           |
| character_set_results    | utf8                                             |
| character_set_server     | utf8                                             |
| character_set_sort       | binary                                           |
| character_set_system     | utf8mb4                                          |
| character_sets_dir       | /opt/gbase/gcluster/server/share/gbase/charsets/ |
+--------------------------+--------------------------------------------------+
10 rows in set (Elapsed: 00:00:00.00)

gbase> set _t_gcluster_support_cte=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> with a as (select * from t_autoinc) select * from a where id<10;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  4 | Second |
|  7 | Third  |
|  8 | BBBB   |
|  9 | CCCC   |
+----+--------+
5 rows in set (Elapsed: 00:00:00.06)

cte表可以被后面的cte表使用

with a as (select * from t1),b as (select * from a) select * from b;

如果放在前面，语法解析时会报表不存在

gbase> with c as (select * from b),a as (select * from t1),b as (select * from a) select * from b;
ERROR 1146 (42S02): Table 'testdb.b' doesn't exist