sql-server - 在 SQL 中生成连续月份

Question

我有一个如下示例的表格：

code |  date       | value
1000   2016-08-05    5000
1000   2016-12-27    8000
1000   2018-03-19    6000
1000   2018-06-02    6000

现在我需要生成这样的连续月份：

code |  date       | value
1000   2016-08-05    5000
1000   2016-09-05    5000
1000   2016-10-05    5000
1000   2016-11-05    5000
1000   2016-12-27    8000
1000   2017-01-27    8000
1000   2017-02-27    8000
........
1000   2018-03-19    6000
1000   2018-04-19    6000
1000   ....

该序列继续进行，直到达到代码的最大日期。在此示例中，代码 1000 的最大日期为 2018-06-02。如何生成月份序列？任何帮助将非常感激。

标签： sql-servertsqldatedatetimerecursive-query

Answer 1

尝试这个：

DECLARE @DataSource TABLE
(
    [Code] INT
   ,[Date] DATE
   ,[value] INT
);

INSERT INTO @DataSource ([Code], [Date], [value])
VALUES (1000, '2016-08-05', 5000)
      ,(1000, '2016-12-27', 8000)
      ,(1000, '2018-03-19', 6000)
      ,(1000, '2018-06-02', 6000);

WITH Ranges AS
(
    SELECT *
          ,LEAD([Date]) OVER (ORDER BY [Date] ASC) AS [DateEnd]
    FROM @DataSource DS
)
SELECT *
FROM Ranges
CROSS APPLY
(
    SELECT DATEADD(MONTH, [number], [Date])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [Date]) < [DateEnd]
) AutoDates ([GeneratedDate]);

这个想法很简单 - 使用LEAD获取日期范围的上限。DATEADD然后有了开始和结束日期，只需使用该函数生成缺少的月份。