PostgreSQL中WITH查询公用表表达式

在PostgreSQL中，WITH查询（也称为公用表表达式，Common Table Expressions，简称CTE）是一种用于定义临时结果集的语句，这个结果集可以在后续的查询中被引用。WITH查询可以简化复杂的SQL查询，使其更加可读和易于维护。

基本语法

WITH查询的基本语法如下：

WITH cte_name (column1, column2, ...) AS (
    -- 这是一个普通的SQL查询
    SELECT ...
)
-- 这是一个引用前面定义的CTE的查询
SELECT ...
FROM cte_name
-- 还可以有其他JOIN、WHERE等子句

SELECT in WITH

WITH中SELECT的基本价值是将复杂的查询分解成更简单的部分。如下示例:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

在这个例子中，它只显示顶级销售地区的每个产品销售总额。VITH子句定义了两个辅助语句:region sales和top regions，其中region sales的输出用于top region, top region的输出用于主SELECT查询。
本例可以不使用WITH编写，但是我们需要两层嵌套的sub- select。这样做更容易一些。

递归CTE(Recursive Queries)

PostgreSQL还支持递归CTE，这允许你定义一个递归查询，这在处理层次结构数据时非常有用，比如组织结构、目录树等。

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

递归WITH查询的一般形式总是一个非递归项，然后是UNION(或UNION ALL)，然后是递归项，其中只有递归项可以包含对查询自身输出的引用。这样的查询执行如下:
递归查询评估

1. 求非递归项的值。
   对于UNION(但不是UNION ALL)，丢弃重复的行。将所有剩余的行包括在递归查询的结果中，并将它们放在临时工作表中。
2. 只要工作台不是空的，重复以下步骤:
   • 计算递归项，将工作表的当前内容替换为递归自引用。对于UNION(但不是UNION ALL)，丢弃重复的行和重复任何先前结果行的行。在递归查询的结果中包括所有剩余的行，并将它们放在一个临时中间表中。
   • 将工作表的内容替换为中间表的内容，然后清空中间表。

在上面的示例中，工作表在每个步骤中只有一行，并且在连续的步骤中获取从1到100的值。在第100步中，由于存在WHERE子句，因此没有输出，因此查询终止。

递归查询通常用于处理分层或树状结构的数据。下面这个查询是一个有用的例子，它查找产品的所有直接和间接子部件，只给出一个显示直接包含项的表:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity * pr.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

示例：递归查询组织结构

假设我们有一个名为employees的表，其中包含employee_id、name和manager_id（表示该员工的直接上级）。

WITH RECURSIVE employee_hierarchy AS (
    -- 基础情况：选择所有没有上级的员工（即顶层员工）
    SELECT employee_id, name, manager_id, CAST(name