基于SQL集的范围

Question

我如何让SQL重复一些基于集合的操作任意次数而不会循环？如何让SQL对一定范围的数字执行运算？我基本上是在寻找一种基于集合的for循环的方法。

我知道我可以创建一个包含整数（例如1到1000）的小表，然后将其用于该范围内的范围操作。

例如，如果我有该表，则可以选择查找数字100-200的总和，如下所示：

select sum(n) from numbers where n between 100 and 200

有任何想法吗？我有点在寻找适用于T-SQL的东西，但是任何平台都可以。

Answer 1

我认为对您的问题的简短回答是使用WITH子句来生成您自己的子句。

不幸的是，数据库中的大人物没有内置的可查询的数字范围伪表。或者，更一般而言，简单的纯SQL数据生成功能。我个人认为这是一个 巨大的
失败，因为如果这样做，则有可能将当前锁定在过程脚本（T-SQL，PL / SQL等）中的许多代码迁移到纯SQL中，从而对性能和代码复杂性有很多好处。

因此，总的来说，听起来您需要的是即时生成数据的能力。

Oracle和T-
SQL都支持可用于执行此操作的WITH子句。它们在不同的DBMS中的工作方式略有不同，MS称它们为“公用表表达式”，但是它们的形式非常相似。将它们与递归结合使用，可以相当容易地生成数字或文本值的序列。这是可能的样子…

在Oracle SQL中：

WITH
  digits AS  -- Limit recursion by just using it for digits.
    (SELECT
      LEVEL - 1 AS num
    FROM
      DUAL
    WHERE
      LEVEL < 10
    CONNECT BY
      num = (PRIOR num) + 1),
  numrange AS
    (SELECT
      ones.num
        + (tens.num * 10)
        + (hundreds.num * 100)
        AS num
    FROM
      digits ones
      CROSS JOIN
        digits tens
      CROSS JOIN
        digits hundreds
    WHERE
      hundreds.num in (1, 2)) -- Use the WHERE clause to restrict each digit as needed.
SELECT
  -- Some columns and operations
FROM
  numrange
  -- Join to other data if needed

诚然，这很冗长。Oracle的递归功能受到限制。语法笨拙，性能不佳，并且仅限于500个（我认为）嵌套级别。这就是为什么我选择仅对前10个数字使用递归，然后再使用交叉（笛卡尔）联接将它们组合成实际数字。

我自己没有使用SQL Server的Common Table
Expressions，但是由于它们允许自引用，因此与Oracle相比，递归要简单得多。我不知道性能是否具有可比性，以及嵌套限制是多少。

无论如何，递归和WITH子句在创建需要即时生成的数据集的查询时都是非常有用的工具。然后，通过查询该数据集，对值进行操作，您可以获得各种不同类型的生成数据。聚合，重复，组合，排列等。您甚至可以使用此类生成的数据来帮助汇总或深入研究其他数据。

更新：
我只想补充一点，一旦您开始以这种方式使用数据，它就会使您想到新的SQL思考方式。它不仅是一种脚本语言。这是一种相当强大的数据驱动的声明性语言。有时使用起来很痛苦，因为多年来它缺少增强功能以​​帮助减少复杂操作所需的冗余。但是，它非常强大，并且是一种非常直观的方式来处理数据集，将其作为算法的目标和驱动程序。