PostgreSQL WITH 学习笔记

在 PostgreSQL 中，WITH 用于创建一个临时性的、可供查询使用的结果集。它类似于一个命名子查询，是一个非常有用的功能。

基本语法

WITH 子句可以在一个查询中定义一个或多个临时表，在查询主体中使用它们。

WITH query1 AS (SELECT ...), query2 AS (SELECT ...) SELECT ... FROM query1, query2 WHERE ...

WITH 子句由以下部分构成：

• WITH：关键字。
• query1：查询名称。名称必须唯一，并且必须遵循 SQL 标识符的规则。
• SELECT ...：查询定义。定义的结果可以在后面的查询中使用。

实例演示

下面是一个使用 WITH 子句的简单实例：

sqlCopy Code
WITH top_customers AS (
  SELECT customer_id, sum(amount)
  FROM orders
  GROUP BY customer_id
  ORDER BY sum(amount) DESC
  LIMIT 10
)
SELECT c.customer_name, tc.sum
FROM top_customers tc
JOIN customers c ON tc.customer_id = c.customer_id;

上述查询会找到订单总金额最高的前 10 名客户，并将他们的名称和总金额列出来。

另一个实例展示了如何使用递归 WITH 子句：

sqlCopy Code
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT id, link, data, 1, ARRAY[id], false
    FROM graph
  UNION ALL
    SELECT graph.id, graph.link, graph.data, sg.depth + 1, path || graph.id,
           graph.id = ANY(path)
    FROM graph, search_graph sg
    WHERE graph.id = sg.link AND NOT cycle
)
SELECT id, link, data, depth, path
FROM search_graph;

上述查询会找到一个图形中所有节点的层次结构，以及包含每个节点的路径。递归 WITH 子句是一个非常有用的功能，可以帮助你处理复杂的数据结构。

总之，PostgreSQL 的 WITH 子句是一个非常强大和有用的功能，可以帮助你处理复杂的查询和数据结构。当你需要创建临时结果集时，使用它们是一个非常好的选择。