PostgreSQL SQL高级特性_postgresql 高级特性

PostgreSQL SQL高级特性

1. WITH查询

WITH查询这一特性通常被称为CTE(Common Table Expressions)，WITH查询在复杂查询中定义一个辅助语句（临时表），通常这一特性用于复杂查询或递归查询。

1.1 复杂使用CTE:

例1:

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

result:

generate_series
-----------------
            1
            2
            3
1
2
3
4
5

例2:

WITH regional_sales as (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_region AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM resional_sales)
)
SELECT region, 
       product, 
       SUM(quantity) AS product_units,
1
2
3
4
5
6
7
8
9
10
11
12