公用表表达式(也称为:CTE,如在Oracle中也称为子查询因子,)是在SQL中声明变量的唯一方法(除了只有PostgreSQL和Sybase SQL Anywhere支持得WINDOW模糊子句)。
这是一个强大的概念。非常强大。请考虑以下声明:
-- Table variables |
它产生了:
v1 v2 w1 w2 |
使用简单的WITH子句,您可以指定表变量列表(记住:一切都是表),它们甚至可能相互依赖。
这很容易理解。这使得CTE(公用表格表达式)已经非常有用,但真正令人敬畏的是它们被允许递归!考虑以下PostgreSQL示例:
WITH RECURSIVE t(v) AS ( |
它产生了:
v |
它是如何工作的?一旦你看到很多关键词,它就相对容易了。您定义一个公共表表达式,它只有两个UNION ALL子查询。
第一次UNION ALL个子查询是我通常所说的“种子行”。它“种子”(初始化)递归。它可以产生一行或几行,之后我们会递归。记住:一切都是一个表,所以我们的递归将发生在整个表上,而不是单个行/值。
第二次UNION ALL个子查询是递归发生的地方。仔细观察,你会发现它是从中选择的t。即允许第二个子查询从我们即将宣布的CTE中进行选择。递归。因此它也可以访问v列,它已经被使用它的CTE声明过的。
在我们的示例中,我们使用行播种递归(1),然后通过添加来递归v + 1。然后通过设置a LIMIT 5(谨防可能无限的递归 - 就像Java 8 Streams一样)在使用现场停止递归。
附注:图灵完整性
递归CTE使SQL:1999图灵完成,这意味着任何程序都可以用SQL编写!(如果你够疯狂的话)
一个经常出现在博客上的令人印象深刻的例子:Mandelbrot Set,例如http://explainextended.com/2013/12/31/happy-new-year-5/上显示:
WITH RECURSIVE q(r, i, rx, ix, g) AS ( |
在PostgreSQL上运行上面的内容,你会得到类似的东西:
..:-....... = = * = :: - @@@@@ ::::。@ .. * - 。=。 |
令人印象深刻,是吧?