是否可以在不使用临时表的情况下，使用单个select语句在sql中显示两个日期之间的月份

Expected Output :          
9
10
11
12
1
2
3
.
.
.
12
1

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;
SELECT   @StartDate = '20120901'        
        ,@EndDate   = '20140101';
;WITH MONTHS (date)
AS
(
    SELECT @StartDate
    UNION ALL
    SELECT DATEADD(MONTH,1,date)
    FROM MONTHS
    WHERE DATEADD(MONTH,1,date)<=@EndDate
)
SELECT MONTH(date) AS MONTH FROM MONTHS

结果：

MONTH
-----------
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1
(17 row(s) affected)

编辑：根据您更新的要求，您可以使用以下查询来实现这一点：

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;
SELECT   @StartDate = '20120901'        
        ,@EndDate   = '20140101';

SELECT  MONTH(DATEADD(MONTH, x.number, @StartDate)) AS Months
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

结果：

Months
-----------
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1
(17 row(s) affected)

DECLARE @startdt DATETIME, @enddt DATETIME
SELECT @startdt =  '2012-09-01', @enddt =  '2014-01-01'
DECLARE @Months TABLE (Months INT)
INSERT INTO @Months VALUES (MONTH(@startdt))
WHILE @startdt < @enddt
BEGIN
 SET  @startdt = DATEADD(MONTH,1,@startdt)
 INSERT INTO @Months VALUES (MONTH(@startdt))
END
SELECT * FROM @Months