SQL: Queries: CTE

12th April 2021 at 3:46pm
SQL: Queries

CTE 即 Common Table Expressions,使用 WITH 子句表达。

Postgres 的文档没有给太 tutorial 式的内容,我主要参考了下面的博客帖子。

另外我只关心使用 CTE 做查询的能力,但它还有 做修改的能力,这一部分暂无涉及。

电脑上如果 PDF 不展示或者展示不正常,使用 Chrome 并安装 PDF Viewer 插件。其他情况请下载文件查看:introduction-to-cte.pdf

用 Recursive CTE 查询 adjacency list 的例子:

构建出一颗树:

      1
     / \
    2   3
   / \   \
  4   5   6
           \
            7
CREATE TABLE tree (
    id INT,
    parent INT
);

INSERT INTO tree VALUES 
  (1, NULL),
  (2, 1),
  (3, 1),
  (4, 2),
  (5, 2),
  (6, 3),
  (7, 6);

查询 7 到根节点的全部节点:

WITH RECURSIVE route(id, parent) AS
(
  SELECT id, parent FROM tree WHERE id=7
  UNION all 
  SELECT tree.id, tree.parent FROM tree, route WHERE route.parent IS NOT NULL AND route.parent = tree.id
)
SELECT id FROM route;

结果是:

7
6
3
1

TOC