select identity(int,0,1) as id into #t from sysobjectsselect
a.empid,
resultdate = dateadd(dd,b.id,a.fromdate)
from
emptemp a,
#t b
where
a.empid = 12334 and datediff(dd,fromdate,todate)>=b.id
a.empid,
resultdate = dateadd(dd,b.id,a.fromdate)
from
emptemp a,
#t b
where
a.empid = 12334 and datediff(dd,fromdate,todate)>=b.id
(empid varchar(20), fromdate datetime,today datetime)insert #t
select '12334','2005-09-01','2005-09-05' [union]
select '12334','2005-09-06','2005-09-09' [union]
select '12334','2005-10-09','2005-10-29' [union]
select '12335','2005-02-01','2005-02-09' [union]
select '12335','2005-03-04','2005-03-23' /*---生成一个'2005-01-01'到'2005-12-31'的表(可以自己生成多少)--*/
select top 366 identity(int,1,1) as [id],convert(datetime,'2005-01-01',120) as d
into #
from sysobjects a,sysobjects b/*--将日期更改正确---*/
update # set d = dateadd(d,[id],d)/*--查找转换表---*/
select b.empid,a.d from # a,#t b
where a.d>=b.fromdate and a.d<=b.today drop table #
drop table #tempid d
-------------------- ------------------------------------------------------
12334 2005-09-01 00:00:00.000
12334 2005-09-02 00:00:00.000
12334 2005-09-03 00:00:00.000
12334 2005-09-04 00:00:00.000
12334 2005-09-05 00:00:00.000
12334 2005-09-06 00:00:00.000
12334 2005-09-07 00:00:00.000
12334 2005-09-08 00:00:00.000
12334 2005-09-09 00:00:00.000
12334 2005-10-09 00:00:00.000
12334 2005-10-10 00:00:00.000
12334 2005-10-11 00:00:00.000
12334 2005-10-12 00:00:00.000
12334 2005-10-13 00:00:00.000
12334 2005-10-14 00:00:00.000
12334 2005-10-15 00:00:00.000
12334 2005-10-16 00:00:00.000
12334 2005-10-17 00:00:00.000
12334 2005-10-18 00:00:00.000
12334 2005-10-19 00:00:00.000
12334 2005-10-20 00:00:00.000
12334 2005-10-21 00:00:00.000
12334 2005-10-22 00:00:00.000
12334 2005-10-23 00:00:00.000
12334 2005-10-24 00:00:00.000
12334 2005-10-25 00:00:00.000
12334 2005-10-26 00:00:00.000
12334 2005-10-27 00:00:00.000
12334 2005-10-28 00:00:00.000
12334 2005-10-29 00:00:00.000
12335 2005-02-01 00:00:00.000
12335 2005-02-02 00:00:00.000
12335 2005-02-03 00:00:00.000
12335 2005-02-04 00:00:00.000
12335 2005-02-05 00:00:00.000
12335 2005-02-06 00:00:00.000
12335 2005-02-07 00:00:00.000
12335 2005-02-08 00:00:00.000
12335 2005-02-09 00:00:00.000
12335 2005-03-04 00:00:00.000
12335 2005-03-05 00:00:00.000
12335 2005-03-06 00:00:00.000
12335 2005-03-07 00:00:00.000
12335 2005-03-08 00:00:00.000
12335 2005-03-09 00:00:00.000
12335 2005-03-10 00:00:00.000
12335 2005-03-11 00:00:00.000
12335 2005-03-12 00:00:00.000
12335 2005-03-13 00:00:00.000
12335 2005-03-14 00:00:00.000
12335 2005-03-15 00:00:00.000
12335 2005-03-16 00:00:00.000
12335 2005-03-17 00:00:00.000
12335 2005-03-18 00:00:00.000
12335 2005-03-19 00:00:00.000
12335 2005-03-20 00:00:00.000
12335 2005-03-21 00:00:00.000
12335 2005-03-22 00:00:00.000
12335 2005-03-23 00:00:00.000(所影响的行数为 59 行) 数据传不过来,必须把creat和union打上括号才行,你要验证,那么你自己把它消了就可以了。