with MonthList(m)
as
(
select m =1
union all
select m + 1 from MonthList where m < 12
)
select m from MonthList
结果:
1
2
3
4
5
6
7
8
9
10
11
12
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
http://msdn2.microsoft.com/en-us/library/ms190766.aspx