基于SQL集的范围

不幸的是,数据库中的大人物没有内置的可查询数字范围伪表。或者,更一般地说,简单的纯SQL数据生成功能。就我个人而言,我认为这是一个错误 失败,因为如果他们这样做了,那么就有可能将许多目前被程序脚本(T-SQL、PL/SQL等)锁定的代码移动到纯SQL中,这对性能和代码复杂性有很多好处。

总之,从一般意义上讲,您需要的是动态生成数据的能力。

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

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

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

无论如何,递归和WITH子句在创建需要动态生成的数据集的查询时是非常有用的工具。然后通过查询该数据集,对值进行操作,可以获得各种不同类型的生成数据。聚合、复制、组合、置换等。您甚至可以使用这些生成的数据来帮助向上滚动或向下钻取其他数据。

我只想补充一点,一旦您开始以这种方式处理数据,它将为您打开思考SQL的新思路。它不仅仅是一种脚本语言。这是一个相当健壮的数据驱动系统 declarative language . 有时,使用它是一件痛苦的事情,因为多年来,它一直缺乏增强功能来帮助减少复杂操作所需的冗余。但尽管如此,它还是非常强大,并且是一种相当直观的方法,可以将数据集作为算法的目标和驱动程序。

我创建了一个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

这基本上是显示SQL不太理想的因素之一。我在想也许正确的方法是构建一个函数来创建范围。(或发电机。)

我相信你的问题的正确答案基本上是“你不能”。

您可以在SQL2005+中使用公共表表达式来执行此操作。

WITH CTE AS
(
    SELECT 100 AS n
    UNION ALL
    SELECT n + 1 AS n FROM CTE WHERE n + 1 <= 200
)
SELECT n FROM CTE

如果使用SQL Server 2000或更高版本,则可以使用 表数据类型 避免创建普通表或临时表。然后对其使用正常的表操作。

使用此解决方案,内存中基本上有一个表结构,可以像实际表一样使用,但性能更高。

我在这里找到了一个很好的讨论: Temporary tables vs the table data type

下面是一个你永远不应该使用的黑客:

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函数简化此过程