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;

它显示了每个产品仅在销售区域的销售总额。WITH子句定义了两个名为 regional_sales 和 top_regions的辅助语句， regional_sales的输出用于top_regions， 而top_regions的输出用于初级的SELECT查询。 这个例子也可以不用WITH来写，但是需要两级嵌套的子SELECT查询。 用这种方法更容易理解。