使用数字范围创建临时表

Question

我有一个表格，其中的行包含一个范围的起始和结束数字，例如：

key     startID       endID
 1         500        505
 2         784        788
 3         802        804

我想创建一个临时表（或表变量/CTE等），其中每个数都有一行，以及它们之间的范围 - 即，给定上述示例，我想看到具有以下行的表：

ID
500
501
502
503
504
505
784
785
786
787
788
802
803
804

有没有人能够指引我一个快速简单的方法来实现这个？我考虑过一些使用数字表格的方法，但是我需要的表格有超过两亿行并且我没有那么大的数字表格！

非常感谢任何帮助。提前致谢。

- LNote

Answer 1

WITH    q AS
        (
        SELECT  startId, endId
        FROM    ranges
        UNION ALL
        SELECT  startId + 1, endId
        FROM    q
        WHERE   startId < endId
        )
SELECT  startId
FROM    q
OPTION  (MAXRECURSION 0)

- Quassnoi

---

@LNote：欢迎。如果答案符合您的要求，请将其标记为已接受。 - Quassnoi

是的 - 我已经回复了，但你太快了，我不得不等待3分钟才能发送！再次感谢。 - LNote

嗯，虽然这对于“选择语句”很有效，但我似乎无法将这个逻辑放入派生表中。你有任何想法吗？如何在一个派生表中模拟这个过程？ - Lukas Eder

我在这里找到了一些更多的想法：http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1。结果证明，cte解决方案不一定是最快的。 - Lukas Eder

@LukasEder：它不是最快的，事实上，它非常慢。通常我会创建一个固定的数字表，或者如果环境允许，使用CRL TVF。但这个查询对于自定义报告或其他一次性查询来说效果还不错。 - Quassnoi

Answer 2

在MSSQL中，您还可以从任意大的表中选择数据，syscolumns是一个例子。如果行数不足，您可以进行交叉连接：

SELECT        TOP 10000
ROW_NUMBER() OVER (ORDER BY c1.id)
FROM          syscolumns AS c1
CROSS JOIN    syscolumns AS c2

- Alexander