基于 SQL 集的范围 [英] SQL set-based range

问题描述

如何让 SQL 重复一些基于集合的操作任意次数而不循环?如何让 SQL 对一系列数字执行操作?我基本上是在寻找一种方法来进行基于集合的 for 循环.

我知道我可以创建一个包含整数的小表，比如从 1 到 1000，然后将它用于该范围内的范围操作.

例如，如果我有那个表，我可以像这样进行选择以找到数字 100-200 的总和:

How can I have SQL repeat some set-based operation an arbitrary number of times without looping? How can I have SQL perform an operation against a range of numbers? I'm basically looking for a way to do a set-based for loop.

I know I can just create a small table with integers in it, say from 1 to 1000 and then use it for range operations that are within that range.

For example, if I had that table I could make a select to find the sum of numbers 100-200 like this:

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

有什么想法吗?我正在寻找适用于 T-SQL 的东西，但任何平台都可以.

Any ideas? I'm kinda looking for something that works for T-SQL but any platform would be okay.

我有自己的解决方案，使用 SQL CLR，它适用于 MS SQL 2005 或 2008.见下文.

I have my own solution for this using SQL CLR which works great for MS SQL 2005 or 2008. See below.

推荐答案

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

I think the very short answer to your question is to use WITH clauses to generate your own.

不幸的是，数据库中的大名称没有内置的可查询数字范围伪表.或者，更一般地说，是简单的纯 SQL 数据生成功能.就个人而言，我认为这是一个巨大的失败，因为如果他们这样做了，就有可能移动当前锁定在过程脚本(T-SQL、PL/SQL 等)中的大量代码.) 转换为纯 SQL，这对性能和代码复杂性有许多好处.

Unfortunately, the big names in databases don't have built-in queryable number-range pseudo-tables. Or, more generally, easy pure-SQL data generation features. Personally, I think this is a huge failing, because if they did it would be possible to move a lot of code that is currently locked up in procedural scripts (T-SQL, PL/SQL, etc.) into pure-SQL, which has a number of benefits to performance and code complexity.

无论如何，从一般意义上讲，您需要的是动态生成数据的能力.

So anyway, it sounds like what you need in a general sense is the ability to generate data on the fly.

Oracle 和 T-SQL 都支持可用于执行此操作的 WITH 子句.它们在不同的 DBMS 中的工作方式略有不同，MS 称它们为通用表表达式"，但它们的形式非常相似.将它们与递归一起使用，您可以相当轻松地生成一系列数字或文本值.这是它可能的样子......

Oracle and T-SQL both support a WITH clause that can be used to do this. They work a little differently in the different DBMS's, and MS calls them "common table expressions", but they are very similar in form. Using these with recursion, you can generate a sequence of numbers or text values fairly easily. Here is what it might look like...

在 Oracle SQL 中:

In 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 位数字使用递归，然后交叉(笛卡尔)连接将它们组合成实际数字.

This is admittedly quite verbose. Oracle's recursion functionality is limited. The syntax is clunky, it's not performant, and it is limited to 500 (I think) nested levels. This is why I chose to use recursion only for the first 10 digits, and then cross (cartesian) joins to combine them into actual numbers.

我自己没有使用过 SQL Server 的公共表表达式，但由于它们允许自引用，递归比在 Oracle 中简单得多.性能是否具有可比性，嵌套限制是什么，我不知道.

I haven't used SQL Server's Common Table Expressions myself, but since they allow self-reference, recursion is MUCH simpler than it is in Oracle. Whether performance is comparable, and what the nesting limits are, I don't know.

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

At any rate, recursion and the WITH clause are very useful tools in creating queries that require on-the-fly generated data sets. Then by querying this data set, doing operations on the values, you can get all sorts of different types of generated data. Aggregations, duplications, combinations, permutations, and so on. You can even use such generated data to aid in rolling up or drilling down into other data.

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

UPDATE: I just want to add that, once you start working with data in this way, it opens your mind to new ways of thinking about SQL. It's not just a scripting language. It's a fairly robust data-driven declarative language. Sometimes it's a pain to use because for years it has suffered a dearth of enhancements to aid in reducing the redundancy needed for complex operations. But nonetheless it is very powerful, and a fairly intuitive way to work with data sets as both the target and the driver of your algorithms.

这篇关于基于 SQL 集的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！