同意楼上 如下 用一个表 t_date 存放 开始日期 -- 结束日期, dt 2007-05-05 2007-05-06 2007-05-07 2007-05-08 2007-05-09 2007-05-10 2007-05-11 2007-05-12 然后 left join select a.dt,isnull(b.值,0) from t_date a left join b on a.dt on b.dt 如有更好,我也学习下
select a.dt,isnull(b.值,0) from t_date a left join b on a.dt on b.dt在2005里报错改成 select a.dt,isnull(b.值,0) from t_date a left join b on a.dt = b.dt
--原始数据:#Test create table #Test(日期 varchar(10),值 int) insert #Test select '2007-05-06',20 union all select '2007-05-07',23 union all select '2007-05-09',15 union all select '2007-05-11',2 union all select '2007-05-12',55select top 31 日期=identity(int,39201,1) into #Date from syscolumns,sysobjects /* 关于标识种子 39201: select datediff(day,0,'2007-05-01') --> 39201 具体显示最早的时间,通过调整标识种子实现。 */select 日期=convert(varchar(10),cast(a.日期 as datetime),120),值=isnull(b.值,0) from #Date a left join #Test b on datediff(day,a.日期,b.日期)=0 /* 日期 值 2007-05-01 0 2007-05-02 0 2007-05-03 0 2007-05-04 0 2007-05-05 0 2007-05-06 20 2007-05-07 23 2007-05-08 0 2007-05-09 15 2007-05-10 0 2007-05-11 2 2007-05-12 55 2007-05-13 0 2007-05-14 0 2007-05-15 0 2007-05-16 0 2007-05-17 0 2007-05-18 0 2007-05-19 0 2007-05-20 0 2007-05-21 0 2007-05-22 0 2007-05-23 0 2007-05-24 0 2007-05-25 0 2007-05-26 0 2007-05-27 0 2007-05-28 0 2007-05-29 0 2007-05-30 0 2007-05-31 0 */--清理现场 drop table #Test,#Date
如下
用一个表 t_date 存放 开始日期 -- 结束日期,
dt
2007-05-05
2007-05-06
2007-05-07
2007-05-08
2007-05-09
2007-05-10
2007-05-11
2007-05-12 然后 left join select a.dt,isnull(b.值,0) from t_date a left join b on a.dt on b.dt 如有更好,我也学习下
select a.dt,isnull(b.值,0) from t_date a left join b on a.dt = b.dt
create table #Test(日期 varchar(10),值 int)
insert #Test
select '2007-05-06',20 union all
select '2007-05-07',23 union all
select '2007-05-09',15 union all
select '2007-05-11',2 union all
select '2007-05-12',55select top 31 日期=identity(int,39201,1) into #Date from syscolumns,sysobjects
/*
关于标识种子 39201:
select datediff(day,0,'2007-05-01') --> 39201
具体显示最早的时间,通过调整标识种子实现。
*/select 日期=convert(varchar(10),cast(a.日期 as datetime),120),值=isnull(b.值,0) from #Date a left join #Test b on datediff(day,a.日期,b.日期)=0
/*
日期 值
2007-05-01 0
2007-05-02 0
2007-05-03 0
2007-05-04 0
2007-05-05 0
2007-05-06 20
2007-05-07 23
2007-05-08 0
2007-05-09 15
2007-05-10 0
2007-05-11 2
2007-05-12 55
2007-05-13 0
2007-05-14 0
2007-05-15 0
2007-05-16 0
2007-05-17 0
2007-05-18 0
2007-05-19 0
2007-05-20 0
2007-05-21 0
2007-05-22 0
2007-05-23 0
2007-05-24 0
2007-05-25 0
2007-05-26 0
2007-05-27 0
2007-05-28 0
2007-05-29 0
2007-05-30 0
2007-05-31 0
*/--清理现场
drop table #Test,#Date