PostgreSQL 高级特性_postgres with t as ()

PostgreSQL 高级特性

WITH查询

是PostgreSQL支持的高级SQL特性之一，这一特性常称CTE(Common Table Expressions)，WITH查询在复杂查询中定义一个辅助语句（可理解成在一个查询中定义的临时表），这一特性常用于复杂查询或递归查询应用场景。

WITH t as(
SELECT generate_series(1,3)
)
SELECT * FROM t;
1
2
3
4

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;     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

递归查询使用CTE

使用RECURSIVE属性可以引用自己的输出

# x从1开始，union加1后的值，循环直到x小于5