SQL SELECT将Min / Max转换为单独的行

I have a table that has a min and max value that I'd like create a row for each valid number in a SELECT statement.

我有一个具有最小值和最大值的表,我想为SELECT语句中的每个有效数字创建一行。

Original table:

| Foobar_ID | Min_Period | Max_Period |
---------------------------------------
| 1         | 0          | 2          |
| 2         | 1          | 4          |

I'd like to turn that into:

我想把它变成:

| Foobar_ID | Period_Num |
--------------------------
| 1         | 0          |
| 1         | 1          |
| 1         | 2          |
| 2         | 1          |
| 2         | 2          |
| 2         | 3          |
| 2         | 4          |

The SELECT results need to come out as one result-set, so I'm not sure if a WHILE loop would work in my case.

SELECT结果需要作为一个结果集出来,所以我不确定WHILE循环是否适用于我的情况。

4 个解决方案

#1

If you expect just a handful of rows per foobar, then this is a good opportunity to learn about recursive CTEs:

如果您希望每个foobar只有少量行,那么这是了解递归CTE的好机会:

with cte as (
      select foobar_id, min_period as period_num, max_period
      from original t
      union all
      select foobar_id, min_period + 1 as period_num, max_period
      from cte
      where period_num < max_period
    )
select foobar_id, period_num
from cte
order by foobar_id, period_num;

You can extend this to any number of periods by setting the MAXRECURSION option to 0.

您可以通过将MAXRECURSION选项设置为0将其扩展到任意数量的句点。

#2

One method would be to use a Tally table, ther's plenty of examples out there, but I'm going to create a very small one in this example. Then you can JOIN onto that and return your result set.

一种方法是使用Tally表,那里有很多例子,但是我将在这个例子中创建一个非常小的例子。然后你可以加入到那里并返回你的结果集。

--Create the Tally Table
CREATE TABLE #Tally (I int);

WITH ints AS(
    SELECT 0 AS i
    UNION ALL
    SELECT i + 1
    FROM ints
    WHERE i + 1 <= 10)
--And in the numbers go!
INSERT INTO #Tally
SELECT i
FROM ints;
GO

--Create the sample table
CREATE TABLE #Sample (ID int IDENTITY(1,1),
                      MinP int,
                      MaxP int);

--Sample data    
INSERT INTO #Sample (Minp, MaxP)
VALUES (0,2),
       (1,4);
GO

--And the solution
SELECT S.ID,
       T.I AS P
FROM #Sample S
     JOIN #Tally T ON T.I BETWEEN S.MinP AND S.MaxP
ORDER BY S.ID, T.I;
GO

--Clean up    
DROP TABLE #Sample;
DROP TABLE #Tally;

#3

Depending on the size of the data and the range of the period, the easiest way to do this is to use a dynamic number fact table, as follows:

根据数据的大小和周期的范围,最简单的方法是使用动态数字事实表,如下所示:

WITH rn AS (SELECT ROW_NUMBER() OVER (ORDER BY object_id) -1 as period_num FROM sys.objects)
SELECT f.foobar_id, rn.period_num
FROM foobar f
    INNER JOIN rn ON rn.period_num BETWEEN f.min_period AND f.max_period

However, if you're working with a larger volume of data, it will be worth creating a number fact table with an index. You can even use a TVV for this:

但是,如果您正在处理大量数据,则值得创建带索引的数字事实表。您甚至可以使用TVV:

-- Declare the number fact table
DECLARE @rn TABLE (period_num INT IDENTITY(0, 1) primary key, dummy int)
-- Populate the fact table so that all periods are covered
WHILE (SELECT COUNT(1) FROM @rn) < (SELECT MAX(max_period) FROM foobar)
    INSERT @rn select 1 from sys.objects

-- Select using a join to the fact table
SELECT f.foo_id, rn.period_num
FROM foobar f
    inner join @rn rn on rn.period_num between f.min_period and f.max_period

#4

Just Create a function sample date and use it

只需创建一个函数样本日期并使用它

CREATE FUNCTION [dbo].[Ufn_GetMInToMaxVal] (@Min_Period INT,@Max_Period INT  )
RETURNS  @OutTable TABLE
(
DATA INT
)
AS
BEGIN

;WIth cte
AS
(
SELECT @Min_Period As Min_Period
UNION ALL
SELECT Min_Period+1 FRom
cte 
WHERE Min_Period <  @Max_Period
)
INSERT INTO @OutTable
SELECT * FROM cte
RETURN
END

Get the result by executing sql statement

通过执行sql语句获取结果

DECLARE @Temp AS TABLE(
        Foobar_ID INT,
        Min_Period INT,
        Max_Period INT
        )
INSERT INTO @Temp

SELECT  1, 0,2 UNION ALL
SELECT  2, 1,4

SELECT Foobar_ID ,
        DATA 
FROM @Temp
CROSS APPLY 
 [dbo].[Ufn_GetMInToMaxVal] (Min_Period,Max_Period)

Result

 Foobar_ID  DATA
 ----------------
    1        0
    1        1
    1        2
    2        1
    2        2
    2        3
    2        4

#1