创建具有数字范围的临时表 [英] Create Temp Table with Range of Numbers

问题描述

我有一个表格，其中包含一个范围的开始和结束编号，例如

I have a table that has rows with the start and end numbers of a range e.g.

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

等等..

我想创建一个临时表(或表变量/cte 等)，其中每个数字都有一行以及它们在它们之间覆盖的范围 - 即给定上面的例子，我想看到一个带有以下几行:

I would like to create a temp table (or table variable/cte etc) that has a row for each of these numbers and the range they cover between them - i.e. given the above example I would like to see a table with the following rows:

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

谁能指出我快速简便的方法来实现这一目标?我想以某种方式使用数字表，但我正在查看的表有 > 200m 行，而且我没有那么大的数字表！

Can anyone point me in the direction of quick and easy way to achieve this? I thought about using a numbers table somehow but the tables I am looking at have > 200m rows and I don't have a numbers table that big!

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

Any help is much appreciated. Thanks in advance.

推荐答案

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)

这篇关于创建具有数字范围的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！