postgresql 中的临时表变量

类似mssql中的临时表变量一样,在postgresql 中可以使用with 来定义临时表变量

WITH regional_sales AS ( --reginal_sales 是临时表变量的表名
    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;

补充:使用临时表变量查询,可以使代码更清晰,并且避免编写重复的代码.