sql - tutorial - scheme语言
基于SQL集的范围 (4)
我如何让SQL重复一些基于集合的操作任意次数没有循环? 我如何让SQL对一系列数字执行操作? 我基本上正在寻找一种方法来做一个基于set的循环。
我知道我可以创建一个整数的小表,比如从1到1000,然后用它来进行范围内的操作。
例如,如果我有这个表,我可以做一个选择找到数字的总和100-200像这样:
select sum(n) from numbers where n between 100 and 200
有任何想法吗? 我有点寻找一些适用于T-SQL的东西,但任何平台都可以。
[编辑]我有我自己的解决方案使用SQL CLR,这对于MS SQL 2005或2008非常适用。 请参见下文。
如果使用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函数来简化此操作