首页 / 知识

关于sql server：基于SQL集的范围

2023-04-15 02:13:00

SQL set-based range

我如何让SQL重复一些基于集合的操作任意次数而不循环？如何让SQL对一定范围的数字执行运算？我基本上是在寻找一种基于集合的for循环的方法。

我知道我可以创建一个带有整数(例如1到1000)的小表，然后将其用于该范围内的范围操作。

例如，如果我有该表，则可以选择查找数字100-200之和，如下所示：

1	SELECT SUM(n) FROM numbers WHERE n BETWEEN 100 AND 200

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

[编辑]我有自己的解决方案(使用SQL CLR)，适用于MS SQL 2005或2008。请参见下文。

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

不幸的是，数据库中的大人物没有内置的可查询的数字范围伪表。或者，更一般而言，简单的纯SQL数据生成功能。我个人认为这是一个巨大的失败，因为如果这样做，则有可能将当前锁定在过程脚本(T-SQL，PL / SQL等)中的许多代码迁移到纯SQL中，从而对性能和代码复杂性有很多好处。

因此，总的来说，听起来您需要即时生成数据的能力。

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

在Oracle SQL中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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表值函数，该函数非常适合此目的。

1 2	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)

这是代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

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;
}
}
}

我像这样部署它：

1
2
3
4
5
6
7
8
9
10

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

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

1
2
3
4
5
6
7

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不理想的那些事情之一。我在想，也许正确的方法是建立一个创建范围的函数。 (或发电机)。

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

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

1
2
3
4
5
6
7
8
9
10
11
12

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

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

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

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

集合循环数字执行