oracle 公共表表达式,7.8. WITH的查询（公用表表达式）

7.8. WITH的查询(公用表表达式)

WITH提供了一种在更大的SELECT查询中编写子查询的方式。

这个通常称为公共表表达式或CTEs的子查询可以认为是定义存在于查询中的临时表。

这个特性的一个应用是用于分解复杂查询为简单的部分。下面是一个例子：

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来编写，但是我们必须需要两个隔离的SELECT嵌套语句。该方法比其它方法更容易理解。

可选的RECURSIVE修饰符从仅有的语法便利性到一个完成事情的特性的改变。

使用RECURSIVE，一个WITH查询可以引用它自己的输出。

一个简单的例子就是查询从1加到100的和：

WITH RECURSIVE t(n) AS (

VALUES (1)

UNION ALL

SELECT n+1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;

一般形式的递归WITH语句总是一个non-recursive term，然后是UNION (或UNION ALL)，那么一个recursive term，

它们只可以包含参考查询的输出。这样的一个查询执行如下：

递归查询评估

评估无递归术语。使用UNION(并不是UNION ALL)，去除重复的行。包括在递归查询结果中所有剩余的行，并将它们放入临时的工作表。

只要工作表不为空，那么将重复这些步骤：

评价递归术语，为递归自我参照替换当前工作表内容。

用UNION(并不是UNION ALL)，去除重复的行和与以前

结果行重复的行。

包括所有在递归查询结果中剩余的行，并将它们放入一个临时的中间表。

以中间表的内容替换工作表的内容，然后清空中间表。

Note:严格的说，该过程是迭代而不是递归，但是RECURSIVE是通过SQL标准委员会选择的术语。

在上面的例子中，在每一步中仅有一个工作表行，并且在后续的步骤中它的值将从1升至100。

在第100步，因为WHERE子句的原因没有任何输出，

因此查询终止。

递归查询通常用于处理分层或树状结构数据。一个有用的示例查询是查找所有直接

或间接的产品的附带部分，仅提供一个表来显示即时的包含：

WITH RECURSIVE included_parts(sub_part， part， quantity) AS (

SELECT sub_part， part， quantity FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part， p.part， p.quantity

FROM included_parts pr， parts p

WHERE p.part = pr.sub_part

)

SELECT sub_part， SUM(quantity) as total_quantity

FROM included_parts

GROUP BY sub_part

当使用递归查询的时候，确保查询的递归部分最终不会返回元组是很重要的，否则查询将会循环下去。

有时，通过使用UNION替代UNION ALL去除掉前面输出重复的行可以实现这个。

然而，通常一个周期不涉及那些完全复制的输出行：检查一个或几个字段来查看是否存在事先达成的

相同点可能是必要的。

处理这种情况的标准方式是计算一个访问队列。

例如，请考虑下面的查询，使用link字段搜索一个表graph：

WITH RECURSIVE search_graph(id， link， data， depth) AS (

SELECT g.id， g.link， g.data， 1

FROM graph g

UNION ALL

SELECT g.id， g.link， g.data， sg.depth + 1

FROM graph g， search_graph sg

WHERE g.id = sg.link

)

SELECT * FROM search_graph;

如果link关系包含循环那么这个查询将会循环。

因为我们需要一个"深度"输出，仅改变UNION ALL为UNION将不会消除循环。

相反，我们需要认识到我们当按照特定路径链接时是否再次得到了相同的行。

我们添加两列path和cycle到倾向循环的查询：

WITH RECURSIVE search_graph(id， link， data， depth， path， cycle) AS (

SELECT g.id， g.link， g.data， 1，

ARRAY[g.id]，

false

FROM graph g

UNION ALL

SELECT g.id， g.link， g.data， sg.depth + 1，

path || g.id，

g.id = ANY(path)

FROM graph g， search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

SELECT * FROM search_graph;

除了防止循环，该数组值通常是有用的，在它的右边作为代表采取的得到任何特定行的"路径"。

在一般情况下，使用一个行数组多于一个字段需要检查到一个循环。

例如，如果我们需要对比字段f1和f2：

WITH RECURSIVE search_graph(id， link， data， depth， path， cycle) AS (

SELECT g.id， g.link， g.data， 1，

ARRAY[ROW(g.f1， g.f2)]，

false

FROM graph g

UNION ALL

SELECT g.id， g.link， g.data， sg.depth + 1，

path || ROW(g.f1， g.f2)，

ROW(g.f1， g.f2) = ANY(path)

FROM graph g， search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

SELECT * FROM search_graph;

Tip:在常见的情况下，当只有一个字段需要检查到循环的时候忽略ROW()语法。

这允许一个简单的数组而不是使用一个复杂类型的数组获得效率。

Tip: 递归查询评估算法产生以广度优先搜索顺序的输出。

你可以按照深度优先查询排序通过通过外部查询ORDER BY一个"path"列来显示结果。

当你不能确定它们在设置了一个LIMIT父查询后是否会循环的时候，这是一个

对于测试查询有用的技巧。

例如，这个循环将在没有LIMIT的情况下循环：

WITH RECURSIVE t(n) AS (

SELECT 1

UNION ALL

SELECT n+1 FROM t

)

SELECT n FROM t LIMIT 100;

它能工作是因为PostgreSQL的实现评估只有当许多实际上是通过父

查询获取的WITH查询行。

在实际的生产环境下不推荐使用该技巧，因为其它的系统可以以不同的方式工作。

同样，如果你使用外部查询将递归查询结果或将它们加入到别的表中分类，那么它通常是不工作的。

一个有用的WITH查询属性是每个父查询执行一次它们做一次评估，即使指定它们

不止一次地通过父查询或WITH查询。

所以，复杂的需要在多个地方放置的计算可以通过设置WITH查询来避免冗余工作。

另一个可能的应用是防止不必要的多副作用函数的评估。

然而，另一方面，比起普通的子查询，优化器是不能够避开父查询拆分为一个WITH查询的限制。

通常将WITH查询评估如上，没有行限制的父查询可能丢失。(但是，正如上面所说，

如果查询参考查询数量有限的行，评估可能会很早终止。)