公共表表达式 (CTE)
描述
公用表表达式 (CTE) 定义了一个临时结果集，用户可以在 SQL 语句的范围内多次引用。CTE 主要用于 SELECT 语句。
语法
WITH common_table_expression [ , ... ]
当
common_table_expression
被定义为
expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query )
参数
-
expression_name
指定公共表表达式的名称。
-
query
一个 SELECT 语句 。
示例
-- 具有多个列别名的CTE
WITH t(x, y) AS (SELECT 1, 2)
SELECT * FROM t WHERE x = 1 AND y = 2;
+---+---+
| x| y|
+---+---+
| 1| 2|
+---+---+
-- CTE中的CTE定义
WITH t AS (
WITH t2 AS (SELECT 1)
SELECT * FROM t2
)
SELECT * FROM t;
+---+
| 1|
+---+
| 1|
+---+
-- CTE在子查询中
SELECT max(c) FROM (
WITH t(c) AS (SELECT 1)
SELECT * FROM t
);
+------+
|max(c)|
+------+
| 1|
+------+
-- CTE在子查询表达式中
SELECT (
WITH t AS (SELECT 1)
SELECT * FROM t
);
+----------------+
|scalarsubquery()|
+----------------+
| 1|
+----------------+
-- CTE在CREATE VIEW语句中
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中检测到名称冲突，默认情况下将抛出AnalysisException。
-- 设置spark.sql.legacy.ctePrecedencePolicy = CORRECTED（建议使用），
-- 内部CTE定义优先于外部定义。
SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED;
WITH
t AS (SELECT 1),
t2 AS (
WITH t AS (SELECT 2)
SELECT * FROM t
)
SELECT * FROM t2;
+---+
| 2|
+---+
| 2|
+---+