WITH AS¶

说明¶

给子查询指定临时别名构建公用表表达式，通过引用别名来使用这些公用表表达式，使查询语句的层次结构更加清晰。公共表表达式（Common Table Expr）又称 CTE ，可以在同一查询中被多次引用，神通的CTE支持递归查询。递归 CTE 是 CTE 的一种特殊形式，它允许在一个表达式内部递归地引用自身，通常用于处理层次结构数据，例如树形结构或者组织结构。

WITH AS 子句能够使查询的层次结构更加清晰，通过 AS 关键字前面的别名和 AS 关键字后面的定义 CTE 语句，在以后的一些需要的地方就可以通过对别名的引用来执行具体的查询内容。当 CTE 不是递归查询且是简单查询时，神通数据库会将这些子查询语句进行优化，将他们当成是内置视图或者一些临时的数据库表。

语法¶

with_as ::=

with_as::=
    WITH [ RECURSIVE ]
    { alias AS ( { subquery } [ UNION [ ALL ] subquery] [ ... ] ) } [ , ... ]
    query ;

参数¶

RECURSIVE

如果声明了此关键字，或者开启参数 COMPATABLE_DBMS = 1 或 5 ，使当前数据库是兼容 Oracle，则可以通过在定义 CTE 的集合操作中引用该 CTE 的名称来实现递归查询。

alias

是子查询的别名，可指定不大于 CTE 查询列数的列名。对于以 UNION [ALL] 的集合操作语句定义的 CTE ，若没有在定义中指定列名，该 CTE 的列名取决于集合操作最左端子查询的列名。

subquery

是子查询语句内容。

注解

对于递归 CTE ，引用 CTE 名必须出现在集合操作最右端子查询，并有且仅引用一次。如果声明了是递归 CTE ，但在定义语句中未对 CTE 名引用，则当成普通 CTE 查询。如果声明了是递归 CTE ，定义语句必须由非递归部分和递归部分使用 UNION [ALL] 集合组成，递归部分必须在集合操作的最右端。

UNION [ALL]

对于递归 CTE ，在定义语句中需要使用此关键字进行集合操作，使用 UNION 会对获取的结果去重，UNION ALL 则会获取所有结果。

注解

使用 UNION 时需要对结果去重，因此需要建立哈希表，通过 HASH_MAX_MEM_SIZE 参数控制去重结果的哈希表的大小；执行时的元组缓存（即工作表）使用的内存大小上限由 SORT_MEM 参数控制。

query

查询内容。

注解

神通数据库的 WITH AS 语句目前仅仅能够在 SELECT 语句中使用，还不能够在 UPDATE 、 INSERT 、 DELETE 中使用；并且暂不支持将 WITH AS 语句作为子查询、暂不支持 search 和 cycle 功能。
不能在递归部分中将 CTE 名字作为外连接的右表。
不支持在递归部分中使用 ORDER BY。
不支持在递归部分中使用聚合函数。
递归部分中不允许使用 FULL JOIN。
不允许在递归部分的 WHERE 语句的子查询中使用 CTE 的名字;递归部分中 SELECT 后面不允许出现引用 CTE 名字的子查询。
不允许在递归部分中使用 LIMIT/OFFSET。
不允许在递归部分中使用 FOR UPDATE。
递归和非递归部分数值类型必须一致。

示例¶

示例1: 使用 WITH AS 语句使查询层次更加清晰

--  清理环境
DROP TABLE tab1 CASCADE;
DROP TABLE tab2 CASCADE;


--  创建表并插入数据
CREATE TABLE tab1(a1 INT, b1 INT);

INSERT INTO tab1 VALUES(1, 1);
INSERT INTO tab1 VALUES(2, 221);
INSERT INTO tab1 VALUES(3, 223);

CREATE TABLE tab2(a2 INT, b2 INT);

INSERT INTO tab2 VALUES(21, 221);
INSERT INTO tab2 VALUES(222, 223);
INSERT INTO tab2 VALUES(500, 625);



--  使用 WITH AS 语句使查询层次更加清晰
WITH s1 AS (SELECT b1 FROM tab1),
     s2 AS (SELECT a2, b2 FROM tab2 WHERE a2 > 100)
SELECT a2 FROM s1, s2 WHERE s1.b1 = s2.b2;
A2(int)      |
-------------
222          |
总数目：1


--  删除表
DROP TABLE tab2;
DROP TABLE tab1;

示例2: 使用 WITH AS 语句进行递归查询

-- 清理环境
DROP TABLE t1 CASCADE;

--  创建表并插入数据
CREATE TABLE t1(a INT,b INT);
INSERT INTO t1 VALUES(2,3);
INSERT INTO t1 VALUES(3,4);


-- 递归查询
WITH RECURSIVE t(a,b) AS(SELECT 1,'a' UNION SELECT t.a+1,t.b FROM t WHERE t.a<5) SELECT a,b FROM t ORDER BY a,b;
A(int)      |B(varchar)      |
----------------------------
1           |a               |
----------------------------
2           |a               |
----------------------------
3           |a               |
----------------------------
4           |a               |
----------------------------
5           |a               |
总数目：5


-- CTE 的定义语句子查询中有子查询，且只指定其中部分列名
WITH RECURSIVE cte1(a) AS(SELECT * FROM t1),cte2(aa) AS(SELECT * FROM cte1 UNION ALL SELECT 3,4 UNION ALL SELECT * FROM (SELECT cte2.aa+1,cte2.b FROM cte2 WHERE cte2.aa<5) a)SELECT * FROM cte2 ORDER BY 1,2;
AA(int)      |B(int)      |
-------------------------
2            |3           |
-------------------------
3            |3           |
-------------------------
3            |4           |
-------------------------
3            |4           |
-------------------------
4            |3           |
-------------------------
4            |4           |
-------------------------
4            |4           |
-------------------------
5            |3           |
-------------------------
5            |4           |
-------------------------
5            |4           |
总数目：10


-- 去重
WITH RECURSIVE cte1(a) AS(SELECT * FROM t1),cte2(a) AS(SELECT * FROM cte1 UNION ALL SELECT 3,4 UNION SELECT * FROM (SELECT cte2.a+1,cte2.b FROM cte2 WHERE cte2.a<5) a)SELECT * FROM cte2 ORDER BY A,B;
A(int)      |B(int)      |
------------------------
2           |3           |
------------------------
3           |3           |
------------------------
3           |4           |
------------------------
4           |3           |
------------------------
4           |4           |
------------------------
5           |3           |
------------------------
5           |4           |
总数目：7

--  删除表
DROP TABLE t1 CASCADE;

示例3: 使用递归查询处理层次结构数据

-- 清理环境
DROP TABLE d_district;

--  创建表并插入数据
CREATE TABLE d_district
(
    code        VARCHAR(6)      PRIMARY KEY,
    name        VARCHAR(20)     NOT NULL,
    parentcode  VARCHAR(6)
);
INSERT INTO d_district VALUES ('100000',   '中国',     NULL);
INSERT INTO d_district VALUES ('110000',   '北京', '100000');
INSERT INTO d_district VALUES ('110100', '北京市', '110000');
INSERT INTO d_district VALUES ('110101', '东城区', '110000');
ANALYZE d_district;


-- 递归查询
WITH RECURSIVE as_tree_cte AS
(SELECT 0 AS cte_level, a.code, a.name, a.parentcode FROM d_district a WHERE (a.name = '中国')
 UNION ALL
 SELECT wct1.cte_level + 1 AS cte_level, wct2.code, wct2.name, wct2.parentcode FROM as_tree_cte wct1
 INNER JOIN d_district wct2 ON wct2.parentcode = wct1.code)
SELECT * FROM as_tree_cte a ORDER BY a.code;
CTE_LEVEL(int)      |CODE(varchar)      |NAME(varchar)      |PARENTCODE(varchar)      |
-----------------------------------------------------------------------------------
0                   |100000             |中国                 |null                     |
-----------------------------------------------------------------------------------
1                   |110000             |北京                 |100000                   |
-----------------------------------------------------------------------------------
2                   |110100             |北京市                |110000                   |
-----------------------------------------------------------------------------------
2                   |110101             |东城区                |110000                   |
总数目：4

--  删除表
DROP TABLE d_district CASCADE;