对于大型查询，我倾向于使用WITH大量依赖命名结果集.这样可以预先定义结果集，并使主查询更简单.命名结果集也可以帮助提高查询计划的效率，例如postgres将结果集存储在临时表中.

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

示例:

WITH 
  cubed_data AS (
     SELECT 
        dimension1_id,
        dimension2_id,
        dimension3_id,
        measure_id,
        SUM(value) value
     FROM
        source_data
     GROUP BY
        CUBE(dimension1, dimension2, dimension3),
        measure
  ), 
  dimension1_label AS(
     SELECT 
        dimension1_id,
        dimension1_label
     FROM 
        labels 
     WHERE 
        object = 'dimension1'
  ), ...
SELECT 
  *
FROM  
  cubed_data
  JOIN dimension1_label USING (dimension1_id)
  JOIN dimension2_label USING (dimension2_id)
  JOIN dimension3_label USING (dimension3_id)
  JOIN measure_label USING (measure_id)

该示例有些人为设计，但我希望它与内联子查询相比，显示出更高的清晰度.当我为OLAP准备数据时，命名结果集对我有很大的帮助.如果您有/想要创建递归查询，则也必须有命名结果集.

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH至少可以在Postgres，Oracle和SQL Server的当前版本上使用

WITH works at least on current versions of Postgres, Oracle and SQL Server