PostgreSQL 通用表表达式与递归查询

PostgreSQL 通用表表达式，简称为 CTE，它提供了一种语句级别的临时表的功能，以帮助你构建复杂但是清晰的 SQL 语句。

PostgreSQL CTE 语法

PostgreSQL 通用表表达式使用 WITH 关键字定义，下面是 PostgreSQL CTE 的语法：

WITH [RECURSIVE] cte_name [(cte_column_list)] AS (
    cte_definition
)
primary_statement;

这里：

通用表表达式以 WITH 关键字开始。
RECURSIVE 关键字表示此通用表表达式是可递归查询的。它是可选的。
cte_name 是通用表表达式是的名称，相当于临时表的表名。
cte_column_list 是通用表表达式的列名的列表，多个列名使用逗号分隔。它是可选的。
cte_definition 是通用表表达式的辅助语句，它可以是 SELECT, INSERT, UPDATE, 或者 DELETE 语句。
primary_statement 是主要语句，它会用到上面 WITH 中定义的通用表表达式。它可以是 SELECT, INSERT, UPDATE, 或者 DELETE。

PostgreSQL 通用表表达式基本实例

我们将使用 Sakila 示例数据库中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

Sakila 示例数据库中的 film 表存储了 DVD 租赁商店中的影片信息。

假设，您想要确定每个影片的租金是否比它坐在的评级的平均租金高。您可以使用如下带有 CTE 的语句：

WITH file_rating_avg AS (
  SELECT
    rating,
    avg(rental_rate) avg_rental_rate
  FROM film
  GROUP BY rating
)
SELECT
  f.film_id,
  f.title,
  f.rental_rate,
  a.avg_rental_rate,
  f.rental_rate > avg_rental_rate "Greater?"
FROM
  film f,
  file_rating_avg a
WHERE f.rating = a.rating
LIMIT 10;

 film_id |      title       | rental_rate |  avg_rental_rate   | Greater?
---------+------------------+-------------+--------------------+----------
       1 | ACADEMY DINOSAUR |        0.99 | 3.0518556701030928 | f
       2 | ACE GOLDFINGER   |        4.99 | 2.8888764044943820 | t
       3 | ADAPTATION HOLES |        2.99 | 2.9709523809523810 | t
       4 | AFFAIR PREJUDICE |        2.99 | 2.8888764044943820 | t
       5 | AFRICAN EGG      |        2.99 | 2.8888764044943820 | t
       6 | AGENT TRUMAN     |        2.99 | 3.0518556701030928 | f
       7 | AIRPLANE SIERRA  |        4.99 | 3.0348430493273543 | t
       8 | AIRPORT POLLOCK  |        4.99 | 2.9387179487179487 | t
       9 | ALABAMA DEVIL    |        2.99 | 3.0348430493273543 | f
      10 | ALADDIN CALENDAR |        4.99 | 2.9709523809523810 | t
(10 rows)

首先，我们使用如下语句定义了一个通用表表达式，名称为 file_rating_avg:

WITH file_rating_avg AS (
  SELECT
    rating,
    avg(rental_rate) avg_rental_rate
  FROM film
  GROUP BY rating
)

然后，我们在后面的主语句中使用了 file_rating_avg 作为一个临时表。

SELECT
  f.film_id,
  f.title,
  f.rental_rate,
  a.avg_rental_rate,
  f.rental_rate > avg_rental_rate "Greater?"
FROM
  film f,
  file_rating_avg a
WHERE f.rating = a.rating
LIMIT 10;

PostgreSQL 通用表表达式递归查询

PostgreSQL 通用表表达式支持递归查询，这很适合应用在一些存储树形数据的方案中，比如产品的分类、系统的导航菜单等。

接下来，我们设计一个用于保存产品分类的表，然后使用 PostgreSQL 通用表表达式获取一个指定分类以及该分类下的所有的下级分类。

我们要在 testdb 数据库中演示这个示例。请先使用下面的语句创建数据库 testdb ：

CREATE DATABASE testdb;

选择 testdb 数据库为当前数据库：

\c testdb;

使用下面语句创建产品分类表 category:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  parent_id INT,
  CONSTRAINT fk_category
    FOREIGN KEY(parent_id) REFERENCES category(id)
);

我们创建了一个 category 表，它有 id, name, parent_id 三个列，并且 parent_id 列存储上级分类的 id。注意，这里我们使用了外键约束。

使用下面的语句插入一些包含了产品分类信息的行：

INSERT INTO category (id, name, parent_id)
VALUES
  (1, 'ROOT', NULL),
  (2, 'Baby', 1),
  (3, 'Home And Kitchen', 1),
  (4, 'Baby Care', 2),
  (5, 'Feeding', 2),
  (6, 'Gifts', 2),
  (7, 'Safety', 2),
  (8, 'Bedding', 3),
  (9, 'Bath', 3),
  (10, 'Furniture', 3),
  (11, 'Grooming', 4),
  (12, 'Hair Care', 4),
  (13, 'Baby Foods', 5),
  (14, 'Food Mills', 5),
  (15, 'Solid Feeding', 5),
  (16, 'Bed Pillows', 8),
  (17, 'Bed Skirts', 8);

假设，你想要查询 id 为 2 的分类以及它的所有的下级分类，请使用下面的语句：

WITH RECURSIVE cte_categories AS (
  SELECT
    id,
    name,
    parent_id
  FROM category
  WHERE id = 2
  UNION
  SELECT
    c.id,
    c.name,
    c.parent_id
  FROM category c, cte_categories cs
  WHERE cs.id = c.parent_id
)
SELECT *
FROM cte_categories;

 id |     name      | parent_id
----+---------------+-----------
  2 | Baby          |         1
  4 | Baby Care     |         2
  5 | Feeding       |         2
  6 | Gifts         |         2
  7 | Safety        |         2
 11 | Grooming      |         4
 12 | Hair Care     |         4
 13 | Baby Foods    |         5
 14 | Food Mills    |         5
 15 | Solid Feeding |         5
(10 rows)

我们通过 WITH RECURSIVE 定义了一个 CTE cte_categories，然后，我们在主语句中从 cte_categories 查找数据。

结论

本文我们展示了 PostgreSQL 通用表表达式能做到的事情。通过使用通用表表达式，您可以简化你的复杂的 SQL 语句，还可以使用递归查询。

PostgreSQL CTE 语法

PostgreSQL 通用表表达式基本实例

PostgreSQL 通用表表达式递归查询

结论

本页目录