WITH (Common Table Expressions)

一、普通CTE

WITH可以为临时查询结果命名，命名后可以在后续的查询语句中反复引用。相当于一个临时的结果集

语法：

WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
SELECT ... FROM cte_name ...;

例子：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

二、递归CTE

语法：

WITH RECURSIVE cte_name [(column_list)] AS (
    -- 初始化查询提供初始结果集
    SELECT initial_columns
    FROM initial_table
    WHERE initial_condition
    
    UNION [ALL | DISTINCT]
    
    -- 递归部分
    SELECT recursive_columns
    FROM recursive_table
    JOIN cte_name ON join_condition
    WHERE recursive_condition
)
SELECT * FROM cte_name [OPTIONAL_CLAUSES];

例子：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;