------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-24 15:04:39
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id varchar(3),startrq datetime,days int )
Go
Insert into ta
select 'a1', '2008-07-18', 30 union
select 'a2', '2006-06-16' , 60 Go
--Start
select top 100 px = identity(int,0,1) into #t from sysobjectsselect a.id,dateadd(d,b.px,a.startrq)
from ta a full join #t b on a.days > b.px
where id is not nulldrop table #t --Result:
/**/
--End
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-24 15:04:39
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id varchar(3),startrq datetime,days int )
Go
Insert into ta
select 'a1', '2008-07-18', 30 union
select 'a2', '2006-06-16' , 60 Go
--Start
select top 100 px = identity(int,0,1) into #t from sysobjectsselect a.id,dateadd(d,b.px,a.startrq)
from ta a full join #t b on a.days > b.px
where id is not nulldrop table #t --Result:
/**/
--End
-- Author:Flystone
-- Version:V1.001
-- Date:2008-07-24 15:04:39
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id varchar(3),startrq datetime,days int )
Go
Insert into ta
select 'a1', '2008-07-18', 30 union
select 'a2', '2006-06-16' , 60 Go
--Start
select top 100 px = identity(int,0,1) into #t from sysobjectsselect a.id,dateadd(d,b.px,a.startrq)
from ta a full join #t b on a.days > b.px
where id is not nulldrop table #t --Result:
/*
id
---- ------------------------------------------------------
a1 2008-07-18 00:00:00.000
a1 2008-07-19 00:00:00.000
.....
a1 2008-08-12 00:00:00.000
a1 2008-08-13 00:00:00.000
a1 2008-08-14 00:00:00.000
a1 2008-08-15 00:00:00.000
a1 2008-08-16 00:00:00.000
a2 2006-06-16 00:00:00.000
a2 2006-06-17 00:00:00.000
a2 2006-06-18 00:00:00.000
......
a2 2006-08-11 00:00:00.000
a2 2006-08-12 00:00:00.000
a2 2006-08-13 00:00:00.000
a2 2006-08-14 00:00:00.000(所影响的行数为 90 行)*/
--End
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id varchar(2),startrq datetime,days int)
insert into #T
select 'a1','2008-07-18',30 union all
select 'a2','2006-06-16',60;
with cte as
(
select id,startrq,days,id0=cast(1 as int) from #T
union all
select c.id,dateadd(day,c.days,c.startrq),c.days,id0+1
from cte c where id0<=12
)
select id,startrq from cte order by id,startrq/*
id startrq
---- -----------------------
a1 2008-07-18 00:00:00.000
a1 2008-08-17 00:00:00.000
a1 2008-09-16 00:00:00.000
a1 2008-10-16 00:00:00.000
a1 2008-11-15 00:00:00.000
a1 2008-12-15 00:00:00.000
a1 2009-01-14 00:00:00.000
a1 2009-02-13 00:00:00.000
a1 2009-03-15 00:00:00.000
a1 2009-04-14 00:00:00.000
a1 2009-05-14 00:00:00.000
a1 2009-06-13 00:00:00.000
a1 2009-07-13 00:00:00.000
a2 2006-06-16 00:00:00.000
a2 2006-08-15 00:00:00.000
a2 2006-10-14 00:00:00.000
a2 2006-12-13 00:00:00.000
a2 2007-02-11 00:00:00.000
a2 2007-04-12 00:00:00.000
a2 2007-06-11 00:00:00.000
a2 2007-08-10 00:00:00.000
a2 2007-10-09 00:00:00.000
a2 2007-12-08 00:00:00.000
a2 2008-02-06 00:00:00.000
a2 2008-04-06 00:00:00.000
a2 2008-06-05 00:00:00.000(26 行受影响)*/