sql - 是否可以在不使用存储过程或函数的情况下生成 X # 行？

Question

我已经使用 UDF 编写了下面的 VIEW，但我想知道是否有一种方法可以在不需要 UDF 的情况下执行此操作。我能找到的唯一其他选择是 SELECT UNION 硬编码值，但我不喜欢那样。有什么建议还是我应该保持原样？我使用的是 MS SQL Server 2008 R2，所以任何可用的游戏都是免费的。

CREATE FUNCTION GetN(@N int)
RETURNS @numbers TABLE (N int PRIMARY KEY NOT NULL)
AS
BEGIN
    DECLARE @i int
    SET @i = 0
    WHILE (@i < @N)
    BEGIN
        INSERT @numbers SELECT @i
        SET @i = @i + 1
    END
    RETURN
END
GO

CREATE VIEW Last10Weeks AS
SELECT DATEPART(wk, GETDATE())-N as WeekNumber
, DATEADD(day, (N*-7) - 1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()) AS StartDate 
, DATEADD(day, (N*-7) + 5 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()) AS EndDate 
FROM GetN(10)

Answer 1

这是使用递归 CTE 的方法:

WITH 
    TestData as (
        SELECT 1 as N
        UNION ALL
        SELECT N + 1 FROM TestData WHERE N < 10
    )
SELECT 
    N,
    DATEPART(wk, GETDATE())-N as WeekNumber,
    DATEADD(day, (N*-7) - 1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()) AS StartDate,
    DATEADD(day, (N*-7) + 5 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 7, GETDATE()) AS EndDate
FROM
    TestData

“计数”由第二个 SELECT 末尾的“N < 10”部分指定。

打印出来:

N   WeekNumber  StartDate   EndDate
1   39  2011-09-18 15:24:40.800 2011-09-24 15:24:40.800
2   38  2011-09-11 15:24:40.800 2011-09-17 15:24:40.800
3   37  2011-09-04 15:24:40.800 2011-09-10 15:24:40.800
4   36  2011-08-28 15:24:40.800 2011-09-03 15:24:40.800
5   35  2011-08-21 15:24:40.800 2011-08-27 15:24:40.800
6   34  2011-08-14 15:24:40.800 2011-08-20 15:24:40.800
7   33  2011-08-07 15:24:40.800 2011-08-13 15:24:40.800
8   32  2011-07-31 15:24:40.800 2011-08-06 15:24:40.800
9   31  2011-07-24 15:24:40.800 2011-07-30 15:24:40.800
10  30  2011-07-17 15:24:40.800 2011-07-23 15:24:40.800