主页 / dba / 问题 / 297707
Accepted
user2368632
Asked: 2021-08-07 13:08:48 +0800 CST

展开日期范围

  • 772

我有下表，其中包含以下数据：

DECLARE @MyActions TABLE (ActionId INT NOT NULL, ActionDate DATETIME NOT NULL)

INSERT INTO @MyActions VALUES (1, '2021-08-01 01:00:00')
INSERT INTO @MyActions VALUES (2, '2021-08-02 02:00:00')
INSERT INTO @MyActions VALUES (3, '2021-08-03 03:00:00')
INSERT INTO @MyActions VALUES (4, '2021-08-04 04:00:00')

如何在不使用慢表值函数的情况下将每个 ActionID 分解为 10 条附加记录（当前 ActionID 日期的前 5 天和后 5 天）？

ActionID = 1 应该扩展到 11 条记录（不包括时间），Action 2 应该扩展到 11 条记录（不包括时间），等等

展开的结果集

1   2021-07-27 00:00:00.000
1   2021-07-28 00:00:00.000
1   2021-07-29 00:00:00.000
1   2021-07-30 00:00:00.000
1   2021-07-31 00:00:00.000
1   2021-08-01 00:00:00.000
1   2021-08-02 00:00:00.000
1   2021-08-03 00:00:00.000
1   2021-08-04 00:00:00.000
1   2021-08-05 00:00:00.000
1   2021-08-06 00:00:00.000
2   2021-07-28 00:00:00.000
2   2021-07-29 00:00:00.000
2   2021-07-30 00:00:00.000
2   2021-07-31 00:00:00.000
2   2021-08-01 00:00:00.000
2   2021-08-02 00:00:00.000
2   2021-08-03 00:00:00.000
2   2021-08-04 00:00:00.000
2   2021-08-05 00:00:00.000
2   2021-08-06 00:00:00.000
2   2021-08-07 00:00:00.000
3   2021-07-29 00:00:00.000
3   2021-07-30 00:00:00.000
3   2021-07-31 00:00:00.000
3   2021-08-01 00:00:00.000
3   2021-08-02 00:00:00.000
3   2021-08-03 00:00:00.000
3   2021-08-04 00:00:00.000
3   2021-08-05 00:00:00.000
3   2021-08-06 00:00:00.000
3   2021-08-07 00:00:00.000
3   2021-08-08 00:00:00.000
4   2021-07-30 00:00:00.000
4   2021-07-31 00:00:00.000
4   2021-08-01 00:00:00.000
4   2021-08-02 00:00:00.000
4   2021-08-03 00:00:00.000
4   2021-08-04 00:00:00.000
4   2021-08-05 00:00:00.000
4   2021-08-06 00:00:00.000
4   2021-08-07 00:00:00.000
4   2021-08-08 00:00:00.000
4   2021-08-09 00:00:00.000
sql-server sql-server-2014

2 个回答

  1. Best Answer

    将每个源行与 11 个偏移日值中的每一个交叉连接，通过转换为日期来删除时间：

    SELECT
    MA.ActionId, 
    ActionDate = 
        CONVERT(datetime, 
            CONVERT(date, 
                DATEADD(DAY, Offset.d, MA.ActionDate)))
FROM @MyActions AS MA
CROSS JOIN 
(
    VALUES
        (-5), (-4), (-3), (-2), (-1), (0),
        (+1), (+2), (+3), (+4), (+5)
) AS Offset (d);

    db<>小提琴演示

    • 6

  2. 如何在不使用慢表值函数的情况下将每个 ActionID 分解为 10 条附加记录（当前 ActionID 日期的前 5 天和后 5 天）？

    创建一个日历表并加入它。

    • 2

相关问题