# 高分求 sql,该如何处理(2)

www.MyException.Cn，网友分享于：2013-01-01   浏览：5次

while convert(varchar(10),@start,120)!> convert(varchar(10),@end,120)
begin
insert @tmp select convert(varchar(10),@start,120)
set @start=@start+1
end
--以上生成表变量
select tb.date,[count]=isnull(count,0)
from @tmp tb left join ta on tb.date=convert(varchar(10),ta.date,120)
------解决方案--------------------

------解决方案--------------------

create table #temp
(date datetime,
[count] int
)
insert into #temp
select '2007-1-1 ', '1 ' union all select '2007-1-2 ', '2 ' union all select '2007-1-4 ', '3 '

insert into #temp
select top 1 dateadd(dd,1,t.[date]),0 from #temp t
where not exists(select 1 from #temp where [date]=dateadd(dd,1,t.[date]))

select * from #temp order by date
--------------
date count
2007-01-01 00:00:00.000 1
2007-01-02 00:00:00.000 2
2007-01-03 00:00:00.000 0
2007-01-04 00:00:00.000 3

------解决方案--------------------
insert into #temp
select top 1 dateadd(dd,1,t.[date]),0 from #temp t
where not exists(select 1 from #temp where [date]=dateadd(dd,1,t.[date]))
order by date
------解决方案--------------------

------解决方案--------------------

create table T([date] datetime, [count] int)
insert T select '2007-1-1 ', 1
union all select '2007-1-2 ', 2
union all select '2007-1-4 ', 3

select tmp.[date], [count]=isnull(T.[count], 0)
from
(
select [date]= '2007-01-01 '
union all select '2007-01-02 '
union all select '2007-01-03 '
union all select '2007-01-04 '
)tmp
left join T on tmp.[date]=T.[date]

--result
date count
---------- -----------
2007-01-01 1
2007-01-02 2
2007-01-03 0
2007-01-04 3

(4 row(s) affected)