原文:http://sqlblogcasts.com/blogs/tomaztsql/archive/2011/07/04/datetime-tally-table.aspx
生成日期记录表的5种方法(尽管有点像孔乙己的茴字有五种写法,不过对学习t-sql还是很有帮助):
-- 方法1.
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
select
a.dt+b.number as dt
from
(select cast('2010/12/31' as datetime) as dt
) as a
cross join master..spt_values as b
where
type = 'P' and number between 1 and 365
--译注:该方法效率很高,但需要master库的访问权限
-- 方法2.
;create table tc..dt
(id int identity(1,1)
,dt datetime)
;with tab as
(
select cast('2011-1-1' as datetime) as dt, 1 as rn
union all
select dateadd(day,1,dt) as dt,rn + 1 as rn
from tab
where rn < 365
)
insert into dt(dt)
select dt
from tab
option (maxrecursion 365)
-- 方法3.
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
;select
cast('2010/12/31' as datetime)+number as dt
from master..spt_values as b
where
type = 'P' and number between 1 and 365
--译注:同方法1
-- 方法4.
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
select
dateadd(day, isnull((select scope_identity()),2),'2010/12/31')
go 365
--译注:该方法效率很低,不建议使用,如果仅作测试则可以使用
-- 方法5.
;create table tc..dt
(id int identity(1,1)
,dt datetime)
while isnull(@@identity,1) <= 365
begin
insert into dt(dt)
select
dateadd(day, isnull(@@identity,1),'2010/12/31') as dt
end