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

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

[编辑]对于SQL MSR，我有自己的解决方案，该解决方案非常适合MS SQL 2005或者2008. 请参见下文。

解决方案

回答

如果使用SQL Server 2000或者更高版本，则可以使用表数据类型来避免创建普通表或者临时表。然后对它使用普通表操作。

使用此解决方案，我们实质上在内存中拥有一个表结构，几乎可以像实际表一样使用它，但性能更高。

我在这里找到了很好的讨论：临时表与表数据类型

回答

这基本上是揭示SQL不理想的那些事情之一。我在想，也许正确的方法是建立一个创建范围的函数。 (或者发电机。)

我相信，对问题的正确答案基本上是"我们不能"。
(对不起。)

回答

我认为对问题的简短回答是使用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思考方式。它不仅是一种脚本语言。这是一种相当强大的数据驱动的声明性语言。有时使用起来很痛苦，因为多年来它缺少增强功能以​​帮助减少复杂操作所需的冗余。但是，它非常强大，并且是一种将数据集用作算法目标和驱动程序的相当直观的方式。

回答

我创建了一个SQL CLR表值函数，该函数非常适合此目的。

SELECT n FROM dbo.Range(1, 11, 2) -- returns odd integers 1 to 11
SELECT n FROM dbo.RangeF(3.1, 3.5, 0.1) -- returns 3.1, 3.2, 3.3 and 3.4, but not 3.5 because of float inprecision. !fault(this)

这是代码：

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

[assembly: CLSCompliant(true)]
namespace Range {
    public static partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRow", TableDefinition = "n bigint")]
        public static IEnumerable Range(SqlInt64 start, SqlInt64 end, SqlInt64 incr) {
            return new Ranger(start.Value, end.Value, incr.Value);
        }

        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRowF", TableDefinition = "n float")]
        public static IEnumerable RangeF(SqlDouble start, SqlDouble end, SqlDouble incr) {
            return new RangerF(start.Value, end.Value, incr.Value);
        }

        public static void FillRow(object row, out SqlInt64 n) {
            n =  new SqlInt64((long)row);
        }

        public static void FillRowF(object row, out SqlDouble n) {
            n = new SqlDouble((double)row);
        }
    }

    internal class Ranger : IEnumerable {
        Int64 _start, _end, _incr;

        public Ranger(Int64 start, Int64 end, Int64 incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerEnum(_start, _end, _incr);
        }
    }

    internal class RangerF : IEnumerable {
        double _start, _end, _incr;

        public RangerF(double start, double end, double incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerFEnum(_start, _end, _incr);
        }
    }

    internal class RangerEnum : IEnumerator {
        Int64 _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerEnum(Int64 start, Int64 end, Int64 incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public long Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }

    internal class RangerFEnum : IEnumerator {
        double _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerFEnum(double start, double end, double incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public double Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }
}

我像这样部署它：

create assembly Range from 'Range.dll' with permission_set=safe -- mod path to point to actual dll location on disk.
go
create function dbo.Range(@start bigint, @end bigint, @incr bigint)
  returns table(n bigint)
  as external name [Range].[Range.UserDefinedFunctions].[Range]
go
create function dbo.RangeF(@start float, @end float, @incr float)
  returns table(n float)
  as external name [Range].[Range.UserDefinedFunctions].[RangeF]
go

回答

这是我们永远不应该使用的技巧：

select sum(numberGenerator.rank)
from
(
select
    rank =  ( select count(*)  
              from reallyLargeTable t1 
              where t1.uniqueValue > t2.uniqueValue ), 
    t2.uniqueValue id1, 
    t2.uniqueValue id2
from reallyLargeTable t2 
) numberGenerator
where rank between 1 and 10

我们可以使用SQL 2005中的Rank()或者Row_Number函数简化此过程