一个SQL问题，请各路高人前来解答。

[imxjs2011b]

select d._date, d._value
from dayteach d
where d._date between '2011-07-01' and '2011-07-10' 
   and d.dept_id = 60

/*
打印:
2011-07-02   43
2011-07-03   96
2011-07-05   26


我想要是 07-01 到 07-10 每天都打印出来，如果表中没有这天的记录value就显示0
*/

[cd731107]

select dateadd(dd,number,'2011-07-01') as date, isnull(d._value,0)value
from master..spt_values a left join dayteach d
on a.type='p' 
and d._date =dateadd(dd,number,'2011-07-01') 
and d._date<='2011-07-10' 
and d.dept_id = 60

[--小F--]

declare @startdate datetime,@enddate datetime
set @startdate='2011-07-01'
set @enddate='2011-07-10'

select convert(varchar(10),dateadd(day,number,@startdate),120),isnull(b._value),0) 
from
    master..spt_values a left join dayteach b
on
    a. date =dateadd(dd,a.number,'2011-07-01') 
and
    datediff(day,dateadd(day,a.number,@startdate), @enddate)>=0
and
   a.number>0 
and
   a.type='p'
and
   b.dept_id = 60

[koumingjie]

create table dayteach (_date datetime,_value int)
insert into dayteach  
select '2011-07-02',43 union
select '2011-07-03',96 union
select '2011-07-05',26 



select dateadd(d,number,'2011-07-01') AS _date,isnull(d._value,0) AS _value
from master..spt_values 
     left join dayteach d 
        on dateadd(d,number,'2011-07-01')=d._date
where type = 'P' and dateadd(d,number,'2011-07-01') <= '2011-07-10'





_date                   _value
----------------------- -----------
2011-07-01 00:00:00.000 0
2011-07-02 00:00:00.000 43
2011-07-03 00:00:00.000 96
2011-07-04 00:00:00.000 0
2011-07-05 00:00:00.000 26
2011-07-06 00:00:00.000 0
2011-07-07 00:00:00.000 0
2011-07-08 00:00:00.000 0
2011-07-09 00:00:00.000 0
2011-07-10 00:00:00.000 0

(10 行受影响)