https://mp.weixin.qq.com/s/4Oij9Qe7AztgIO_t1Zt6ZQ
SQL 充满了神奇的魔力，尤其是那些高级的关系型数据库系统，它们的 SQL 能力总是能给你带来一些新奇的意外。
公共表表达式或 CTE 简介
公共表表达式是一个临时结果集，您可以在另一个 SQL 语句（包括SELECT、INSERT、UPDATE或DELETE）中引用它。
公共表表达式是临时的，因为它们仅在查询执行期间存在。
创建 CTE 的语法如下：
WITH
cte_name (column_list)
AS
(
CTE_query_definition
)
statement
;
在这个语法中：
首先，指定 CTE 的名称，后跟可选的列列表。
其次，在WITH子句主体内，指定返回结果集的查询。如果您没有在 CTE 名称后显式指定列列表，则CTE_query_definition的查询列表将成为 CTE 的列列表。
第三，在SELECT、INSERT、UPDATE或DELETE等statement中，将 CTE 用作表或视图。
公共表表达式或 CTE 通常用于简化 PostgreSQL 中的复杂连接和子查询。
PostgreSQL CTE 示例
让我们看一些使用 CTE 的示例来更好地理解。
一个简单的 PostgreSQL CTE 示例
我们将使用示例数据库中的film和rental表进行演示。
请参见以下示例：
WITH
cte_film
AS
(
SELECT
film_id,
title,
(
CASE
WHEN
length
<
30
THEN
'Short'
WHEN
length
<
90
THEN
'Medium'
ELSE
'Long'
END
)
length
FROM
film
)
SELECT
film_id,
title,
length
FROM
cte_film
WHERE
length
=
'Long'
ORDER
BY
title;
这是部分输出：
图片
请注意，此示例用于演示目的。
在本例中，我们首先使用WITH子句定义了一个名为cte_film的公共表表达式，如下所示：
WITH
cte_film
AS
(
SELECT
film_id,
title,
(
CASE
WHEN
length
<
30
THEN
'Short'
WHEN
length
>=
30
AND
length
<
90
THEN
'Medium'
WHEN
length
>=
90
THEN
'Long'
END
)
length
FROM
film
)
公共表表达式有两部分：
第一部分定义 CTE 的名称，即cte_film。
第二部分定义一个用行填充表达式的SELECT语句。
然后，我们在SELECT语句中使用名为cte_film的 CTE 仅返回长度为 ‘Long’ 的电影。
将 CTE 与表连接的示例
在下面的示例中，我们将使用rental和staff表：
以下语句说明了如何将 CTE 与表连接：
WITH
cte_rental
AS
(
SELECT
staff_id,
COUNT
(rental_id) rental_count
FROM
rental
GROUP
BY
staff_id
)
SELECT
s.staff_id,
first_name,
last_name,
rental_count
FROM
staff s
INNER
JOIN
cte_rental
USING
(staff_id);
在这个例子中：
首先，CTE 返回一个结果集，其中包括员工 ID 和租赁数量。
然后，使用staff_id列将staff表与 CTE 连接起来。
这是输出：
图片
将 CTE 与窗口函数结合使用的示例
以下语句说明了如何将 CTE 与RANK() 窗口函数一起使用：
WITH
cte_film
AS
(
SELECT
film_id,
title,
rating,
length
,
RANK() OVER (
PARTITION
BY
rating
ORDER
BY
length
DESC
)
length_rank
FROM
film
)
SELECT
*
FROM
cte_film
WHERE
length_rank
=
1
;在这个例子中：
首先，我们定义了一个 CTE，它返回每个电影评级按长度排序的电影排名。
其次，我们只选择长度排名为一的电影。
下图显示了输出：
图片
PostgreSQL CTE 优势
以下是使用公共表表达式或 CTE 的一些优点：
提高复杂查询的可读性。您可以使用 CTE 以更有组织性和可读性的方式组织复杂的查询。
能够创建递归查询。递归查询是引用自身的查询。当您想要查询组织结构图或物料清单等分层数据时，递归查询会派上用场。
与窗口函数结合使用。您可以将 CTE 与窗口函数结合使用来创建初始结果集，并使用另一个 select 语句来进一步处理该结果集。
在本文中，您了解了 PostgreSQL CTE 或公共表表达式来简化复杂查询。
