sql - tutorial - scheme语言

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

有了这个解决方案，你基本上可以在内存中使用一个表结构，几乎可以像真正的表一样使用，但是性能要高得多。

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

我认为你的问题很简短的答案是使用WITH子句来生成你自己的。

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

所以无论如何，这听起来就像你一般意义上所需要的是即时生成数据的能力。

Oracle和T-SQL都支持可用于执行此操作的WITH子句。 他们在不同的数据库管理系统中的工作方式稍有不同，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的公用表表达式，但是由于它们允许自引用，所以递归比在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函数来简化此操作