Search CTRL + K

Common Table Expression

CTESQL 子查询一种方式,一般用于简化复杂查询。CTE 其实是计算过程中使用 WITH 关键词创建一个命名临时 ,使其能被当前查询重复使用。

以下是 CTE 例子。

WITH tmp AS (
WITH tmp (col1, col2) AS (
  SELECT 1, 2
SELECT col1 + col2 FROM tmp;
WITH tmp1 (col1) AS (
), tmp2 (col2) AS (
  SELECT col1 + 1
  FROM tmp1
SELECT col2 FROM tmp2;

递归 CTE

WITH 关键词后加上 RECURSIVE 关键词将允许 CTE 引用自己,使得 SQL 支持递归,是图灵完备语言(可证明),和通用编程语言一样富有表现力。

以下是递归 CTE 例子。

WITH RECURSIVE tmp (counter) AS (
  ( SELECT 1 ) -- 1
  UNION -- 2
  ( SELECT counter + 1 FROM tmp WHERE counter < 10 ) -- 3
  1. anchor member,这部份是递归 CTE 的基础值。
  2. 使用 UNION/UNION ALL 将基础值和递归结果联合。
  3. recursive member,这部份是递归查询部份,引用 CTE 自己,并且包含终止条件。

A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query.[1]

CMU 15-445

Adding the RECURSIVE keyword after WITH allows a CTE to reference itself. This enables the implementation of recursion in SQL queries. With recursive CTEs, SQL is provably Turing-complete, implying that it is as computationally expressive as more general purpose programming languages (if a bit more cumbersome).[2]

  1. ↩︎

  2. ↩︎