10个SQL技巧之二：使用递归SQL生成数据

栏目: 数据库 · 发布时间: 5年前

内容简介：公用表表达式（也称为：CTE，如在Oracle中也称为子查询因子，）是在SQL中声明变量的唯一方法（除了只有PostgreSQL和Sybase SQL Anywhere支持得WINDOW模糊子句）。这是一个强大的概念。非常强大。请考虑以下声明：它产生了：

公用表表达式（也称为：CTE，如在Oracle中也称为子查询因子，）是在 SQL 中声明变量的唯一方法（除了只有PostgreSQL和Sybase SQL Anywhere支持得WINDOW模糊子句）。

这是一个强大的概念。非常强大。请考虑以下声明：

-- Table variables
WITH
  t1(v1, v2) AS (SELECT 1, 2),
  t2(w1, w2) AS (
    SELECT v1 * 2, v2 * 2
    FROM t1
  )
SELECT *
FROM t1, t2

它产生了：

v1 v2 w1 w2 
----------------- 
 1 2 2 4

使用简单的WITH子句，您可以指定表变量列表（记住：一切都是表），它们甚至可能相互依赖。

这很容易理解。这使得CTE（公用表格表达式）已经非常有用，但真正令人敬畏的是它们被允许递归！考虑以下PostgreSQL示例：

WITH RECURSIVE t(v) AS (
  SELECT 1     -- Seed Row
  UNION ALL
  SELECT v + 1 -- Recursion
  FROM t
)
SELECT v
FROM t
LIMIT 5

它产生了：

它是如何工作的？一旦你看到很多关键词，它就相对容易了。您定义一个公共表表达式，它只有两个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 (
  SELECT r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02, 
        .0::DOUBLE PRECISION      , .0::DOUBLE PRECISION, 0
  FROM generate_series(-60, 20) r, generate_series(-50, 50) i
  UNION ALL
  SELECT r, i, CASE WHEN abs(rx * rx + ix * ix) &amp;lt;= 2 THEN rx * rx - ix * ix END + r, 
               CASE WHEN abs(rx * rx + ix * ix) &amp;lt;= 2 THEN 2 * rx * ix END + i, g + 1
  FROM q
  WHERE rx IS NOT NULL AND g &amp;lt; 99
)
SELECT array_to_string(array_agg(s ORDER BY r), '')
FROM (
  SELECT i, r, substring(' .:-=+*#%@', max(g) / 10 + 1, 1) s
  FROM q
  GROUP BY i, r
) q
GROUP BY i
ORDER BY i

在PostgreSQL上运行上面的内容，你会得到类似的东西：

                             ..：-....... = = * = :: - @@@@@ :::：。@ .. * - 。=。
                             ... = ... = ... :: +％@：@@@@@@@@@@@@@ + *＃= =：+ - 。..-   
                             。：。：= :: * .... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ..：。
                             ... * @@@@ = @：@@@@@@@@@@@@@@@@@@@@@@@@@@ = = ....：。...： ：。
                              :: @@@@@： - @@@@@@@@@@@@@@@@@@@@@@@@@@@@：@ ..-：@ = * ::: 。
                              .- @@@@@ - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ = @@@@ = .. ：
                              ... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 。
                             ....： - * @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@： ：   
                            ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ..  
                          ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ - ： ......   
                         .--：+。@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@ ...   
                         == @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@  -  ..   
                         .. + @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@ - ＃。  
                         ... = + @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@ .. 
                         - 。=  -  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@ ..：
                        。*％：@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@：@  - 
 。..：... ..- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@ 
.............. ....- @@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@％@ =
- = - .....-：。.......... :: @@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@ .. 
.. =：-.... = @ + .. = .... ..... @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@ :. 
：+ @@ :: @ == @ - *：％：+ .......：@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。
:: @@@ - @@@@@@@@@ - ：= .....：@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@：
：@@@@@@@@@@@@@@@ =： .....％@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@ 
：@@@@@@@@@@@@@@@@@ -...：@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@： - 
：@@@@@@ @@@@@@@@@@@@@ - ..％@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@。
％@@@@@@@@@@@@@@@@@@@ -..- @@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。
@@@@@@@@@@@@@@@@@@@@@ :: + @@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ +
@@@@@@@@@@@@@@@@@@@@@@：@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@ .. 
@@@@@@@@@@@@@@@@@@@ @@@ - @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@  - 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@。

令人印象深刻，是吧？

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

算法导论（原书第3版）

Thomas H.Cormen、Charles E.Leiserson、Ronald L.Rivest、Clifford Stein / 殷建平、徐云、王刚、刘晓光、苏明、邹恒明、王宏志 / 机械工业出版社 / 2012-12 / 128.00元

在有关算法的书中，有一些叙述非常严谨，但不够全面；另一些涉及了大量的题材，但又缺乏严谨性。本书将严谨性和全面性融为一体，深入讨论各类算法，并着力使这些算法的设计和分析能为各个层次的读者接受。全书各章自成体系，可以作为独立的学习单元；算法以英语和伪代码的形式描述，具备初步程序设计经验的人就能看懂；说明和解释力求浅显易懂，不失深度和数学严谨性。全书选材经典、内容丰富、结构合理、逻辑清晰，对本科......一起来看看《算法导论（原书第3版）》这本书的介绍吧!

码农工具

10个SQL技巧之二：使用递归SQL生成数据

算法导论（原书第3版）

随机密码生成器

SHA 加密

正则表达式在线测试