时间戳

首页 > 解决方案 > 使用 CTE 的多个日期范围

sql-server - 使用 CTE 的多个日期范围

问题描述

我需要生成一个半小时的表格。我有以下工作：

WITH ctePeriods AS
(
  SELECT  @gapStart HalfHourPeriod
  UNION ALL  
  SELECT  DATEADD(MINUTE, 30, HalfHourPeriod)
  FROM    ctePeriods  
  WHERE   HalfHourPeriod <  DATEADD(MINUTE, -30, @gapEnd)
)

这给了我@gapStart 和@gapEnd 之间范围的值。

但是，我还有一个需要生成的范围表：

create table #gaps(HHFrom datetime, HHTo datetime)

目前我正在使用它来获取上面使用的@gapStart 和@gapEnd 的值，方法是从#gaps 获取最小值和最大值。但这意味着我要填写比 ctePeriods 需要的更多行。

有什么方法可以在 ctePeriods 中使用 #gaps 中的行，所以我只创建我需要的行？

标签： sql-servercommon-table-expression

解决方案

我个人更喜欢使用 Tally Table 来处理这样的事情。您可以使用持久的 Tally 表，或者动态创建一个（就像我在这里所做的那样）：

CREATE TABLE #gaps (HHFrom datetime,
                    HHTo datetime);

INSERT INTO #gaps (HHFrom,
                   HHTo)
VALUES('20190101','20190103'),
      ('20190217','20190315'),
      ('20190708',GETDATE());
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3, N N4, N N5, N N6), --1000000 rows, feel free to increase/decrease per your own requirement
Dates AS(
    SELECT G.HHFrom,
           G.HHTo,
           DATEADD(MINUTE, 30*T.I, G.HHFrom) AS HH
    FROM #gaps G
         CROSS JOIN Tally T
    WHERE DATEADD(MINUTE, 30*T.I, G.HHFrom) <= G.HHTo)
SELECT *
FROM Dates D
ORDER BY D.HHFrom, D.HH;

GO

DROP TABLE #gaps;

与 rCTE 不同，这意味着对于较大的范围，如果您的行数超过 100 行（默认递归），该语句不会“失败”，并且不像 rCTE 那样递归。

推荐阅读