公用表表达式
描述
定义一个可在 SQL 语句的范围内多次引用的临时结果集。 CTE 主要在 SELECT 语句中使用。
语法
WITH common_table_expression [, ...]
common_table_expression
view_identifier [ ( column_identifier [, ...] ) ] [ AS ] ( query )参数
view_identifier
可按其引用 common_table_expression 的标识符
column_identifier
可按其引用 common_table_expression 的列的可选标识符。
如果指定了 column_identifier，则其数量必须与 query 返回的列数相匹配。 如果未指定任何名称，则列名称派生自 query。
query
生成结果集的查询
示例
-- CTE with multiple column aliases
WITH t(x, y) AS (SELECT 1, 2)
SELECT * FROM t WHERE x = 1 AND y = 2;
+---------+-----------+
| x| y|
+---------+-----------+
| 1| 2|
+---------+-----------+
-- CTE in CTE definition
WITH t AS (
WITH t2 AS (SELECT 1)
SELECT * FROM t2)
SELECT * FROM t;
+---------+
| 1|
+---------+
| 1|
+---------+
-- CTE in subquery
SELECT max(c) FROM (
WITH t(c) AS (SELECT 1)
SELECT * FROM t);
+---------+
| max(c)|
+---------+
| 1|
+---------+
-- CTE in subquery expression
SELECT (WITH t AS (SELECT 1)
SELECT * FROM t);
+------------------+
| scalasubquery()|
+------------------+
| 1|
+------------------+
-- CTE in CREATE VIEW statement
CREATE VIEW v AS
WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
SELECT * FROM t;
SELECT * FROM v;
+---------+-----------+----------+----------+
| a| b| c| d|
+---------+-----------+----------+----------+
| 1| 2| 3| 4|
+---------+-----------+----------+----------+
-- CTE names are scoped
WITH t AS (SELECT 1),
t2 AS (
WITH t AS (SELECT 2)
SELECT * FROM t)
SELECT * FROM t2;
+---------+
| 2|
+---------+
| 2|
+---------+