sql - t-sql select 获取年份范围内的所有月份

Question

我需要一个选择来返回指定日期范围内的月份和年份，我将在其中输入开始年份和月份，并且选择将返回从我输入的日期到今天的月份和年份。

我知道我可以在循环中执行此操作，但我想知道是否可以在一系列选择中执行此操作？

Year  Month
----  -----
2010  1
2010  2
2010  3
2010  4
2010  5
2010  6
2010  7

等等。

Answer 1

天哪，伙计们……使用“计数递归 CTE”或“rCTE”与使用循环一样糟糕甚至更糟。请参阅下面的文章了解我为什么这么说。

http://www.sqlservercentral.com/articles/T-SQL/74118/

这是一种无需任何 RBAR(包括计数 rCTE 的“隐藏 RBAR”)即可完成此操作的方法。

--===== Declare and preset some obviously named variables
DECLARE @StartDate DATETIME,
        @EndDate   DATETIME
;
 SELECT @StartDate = '2010-01-14', --We'll get the month for both of these 
        @EndDate   = '2020-12-05'  --dates and everything in between
;
WITH
cteDates AS
(--==== Creates a "Tally Table" structure for months to add to start date
     -- calulated by the difference in months between the start and end date.
     -- Then adds those numbers to the start of the month of the start date.
 SELECT TOP (DATEDIFF(mm,@StartDate,@EndDate) + 1)
        MonthDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate) 
                  + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
--===== Slice each "whole month" date into the desired display values.
 SELECT [Year]  = YEAR(MonthDate),
        [Month] = MONTH(MonthDate) 
   FROM cteDates
;