try: select dateadd(dd,a.num,b.起始日期) as 任务日期, 期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额, b.任务人 from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a, tabname b where datediff(dd,b.起始日期,b.终止日期)>=a.num order by 任务人,任务日期
--生成测试数据 declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10)) insert into @tname select '2009-01-01','2009-01-5',100,'a1' insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助系统表生成辅助列以实现功能 select dateadd(dd,a.num,b.起始日期) as 任务日期, 期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额, b.任务人 from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a, @tname b where datediff(dd,b.起始日期,b.终止日期)>=a.num order by 任务人,任务日期--输出结果 /* 任务日期 本日任务额 任务人 ------------------------------------------------------ ----------- ---------- 2009-01-01 00:00:00.000 20 a1 2009-01-02 00:00:00.000 20 a1 2009-01-03 00:00:00.000 20 a1 2009-01-04 00:00:00.000 20 a1 2009-01-05 00:00:00.000 20 a1 2009-01-01 00:00:00.000 120 a2 2009-01-02 00:00:00.000 120 a2 */
Frankly,在楼主的表中,如果起始日期与终止日期之间的日期跨度太大,可能超出通过系统表生成的序列范围。 如果这样的假定存在,可以借助临时表/表变量等手段生成一个大的时间序列,然后再关联查询: For Example:--生成测试数据 declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10)) insert into @tname select '2009-01-01','2009-01-5',100,'a1' insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助临时表生成一个从0起始的1000连续数据的序列 select top 1000 identity(int,0,1) as num into # from sysobjects a,syscolumns b --借助系统表生成辅助列以实现功能 select dateadd(dd,a.num,b.起始日期) as 任务日期, 期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额, b.任务人 from # a,@tname b where datediff(dd,b.起始日期,b.终止日期)>=a.num order by 任务人,任务日期--删除临时表 drop table #
--生成测试数据 declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10)) insert into @tname select '2009-01-01','2009-01-5',100,'a1' insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助表变量生成一个从0起始的1000连续数据的序列 declare @tnum table(num int identity(0,1),id int) insert into @tnum(id) select top 1000 a.id from sysobjects a,syscolumns b--借助系统表生成辅助列以实现功能 select dateadd(dd,a.num,b.起始日期) as 任务日期, 期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额, b.任务人 from @tnum a,@tname b where datediff(dd,b.起始日期,b.终止日期)>=a.num order by 任务人,任务日期 --输出结果 /* 任务日期 本日任务额 任务人 ------------------------------------------------------ ----------- ---------- 2009-01-01 00:00:00.000 20 a1 2009-01-02 00:00:00.000 20 a1 2009-01-03 00:00:00.000 20 a1 2009-01-04 00:00:00.000 20 a1 2009-01-05 00:00:00.000 20 a1 2009-01-01 00:00:00.000 120 a2 2009-01-02 00:00:00.000 120 a2 */
select
dateadd(dd,a.num,b.起始日期) as 任务日期,
期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
b.任务人
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a,
tabname b
where
datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
任务人,任务日期
--生成测试数据
declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10))
insert into @tname select '2009-01-01','2009-01-5',100,'a1'
insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助系统表生成辅助列以实现功能
select
dateadd(dd,a.num,b.起始日期) as 任务日期,
期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
b.任务人
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a,
@tname b
where
datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
任务人,任务日期--输出结果
/*
任务日期 本日任务额 任务人
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 20 a1
2009-01-02 00:00:00.000 20 a1
2009-01-03 00:00:00.000 20 a1
2009-01-04 00:00:00.000 20 a1
2009-01-05 00:00:00.000 20 a1
2009-01-01 00:00:00.000 120 a2
2009-01-02 00:00:00.000 120 a2
*/
如果这样的假定存在,可以借助临时表/表变量等手段生成一个大的时间序列,然后再关联查询:
For Example:--生成测试数据
declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10))
insert into @tname select '2009-01-01','2009-01-5',100,'a1'
insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助临时表生成一个从0起始的1000连续数据的序列
select top 1000 identity(int,0,1) as num into # from sysobjects a,syscolumns b
--借助系统表生成辅助列以实现功能
select
dateadd(dd,a.num,b.起始日期) as 任务日期,
期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
b.任务人
from
# a,@tname b
where
datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
任务人,任务日期--删除临时表
drop table #
declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10))
insert into @tname select '2009-01-01','2009-01-5',100,'a1'
insert into @tname select '2009-01-01','2009-01-2',240,'a2' --借助表变量生成一个从0起始的1000连续数据的序列
declare @tnum table(num int identity(0,1),id int)
insert into @tnum(id) select top 1000 a.id from sysobjects a,syscolumns b--借助系统表生成辅助列以实现功能
select
dateadd(dd,a.num,b.起始日期) as 任务日期,
期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
b.任务人
from
@tnum a,@tname b
where
datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
任务人,任务日期
--输出结果
/*
任务日期 本日任务额 任务人
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 20 a1
2009-01-02 00:00:00.000 20 a1
2009-01-03 00:00:00.000 20 a1
2009-01-04 00:00:00.000 20 a1
2009-01-05 00:00:00.000 20 a1
2009-01-01 00:00:00.000 120 a2
2009-01-02 00:00:00.000 120 a2
*/