求一条关于日期的查询语句

Question

同意二楼看法，你先建立一个表B比如
时间        数量  
2005-1-1      0
2005-1-8      0
2005-1-9      0
2005-1-10     0
2005-1-11     0
2005-1-12     0  
2005-1-13     0
………………
2005-12-14    0这个表，你可以在sql server 中生成，然后导出然后 update 表A set 数量 = 表b.数量
     from 表b
     where 表a.日期=表b.日期

Answer 1

--测试数据
create table t (时间 datetime,数量 int)
insert t select '2005-9-7', 44
insert t select '2005-9-9',33
insert t select '2005-9-11',22
insert t select '2005-9-14',33  declare @d datetime
set @d='2005-9-7'
while @d<'2005.09.14'
begin
 insert t select top 1 @d,0 from t where 时间<>@d
 set @d=dateadd(day,1,@d)
endselect * from t order by 时间--结果
时间                                                     数量          
------------------------------------------------------ ----------- 
2005-09-07 00:00:00.000                                44
2005-09-07 00:00:00.000                                0
2005-09-08 00:00:00.000                                0
2005-09-09 00:00:00.000                                0
2005-09-09 00:00:00.000                                33
2005-09-10 00:00:00.000                                0
2005-09-11 00:00:00.000                                0
2005-09-11 00:00:00.000                                22
2005-09-12 00:00:00.000                                0
2005-09-13 00:00:00.000                                0
2005-09-14 00:00:00.000                                33（所影响的行数为 11 行）--删除测试数据
drop table t
go

Answer 2

declare @tb table
(
  [时间] datetime,
  [数量] int
)
insert @tb
select '2005-9-7',44 union
select '2005-9-9',33 union
select '2005-9-11',22 union
select '2005-9-14',13 --查询
declare @min datetime,@max datetime
select @min=dateadd(day,-1,min([时间])),
       @max=max([时间])
from @tb
select  A.dt
        ,isnull(B.[数量],0) as [数量]
from (
        select dateadd(day,ID,@min) as dt
        from (
               select top 100 percent 
                      (select count(1) 
                          from sysobjects 
                             where id<=t.id) as 'ID'
               from sysobjects t 
               order by t.id 
             )tb
        where dateadd(day,ID,@min)<=@max
     )A
left join @tb B on datediff(day,A.dt,B.[时间])=0--结果
/*
dt                                                     数量          
------------------------------------------------------ ----------- 
2005-09-07 00:00:00.000                                44
2005-09-08 00:00:00.000                                0
2005-09-09 00:00:00.000                                33
2005-09-10 00:00:00.000                                0
2005-09-11 00:00:00.000                                22
2005-09-12 00:00:00.000                                0
2005-09-13 00:00:00.000                                0
2005-09-14 00:00:00.000                                13（所影响的行数为 8 行）
*/

Answer 3

如果同一条有多条记录要用group by:declare @tb table
(
  [时间] datetime,
  [数量] int
)
insert @tb
select '2005-9-7',44 union
select '2005-9-7',42 union
select '2005-9-9',33 union
select '2005-9-11',22 union
select '2005-9-14',13 --查询
declare @min datetime,@max datetime
select @min=dateadd(day,-1,min([时间])),
       @max=max([时间])
from @tbselect  A.dt
        ,sum(isnull(B.[数量],0)) as [数量]
from (
        select dateadd(day,ID,@min) as dt
        from (
               select top 100 percent 
                      (select count(1) 
                          from sysobjects 
                             where id<=t.id) as 'ID'
               from sysobjects t 
               order by t.id 
             )tb
        where dateadd(day,ID,@min)<=@max
     )A
left join @tb B on datediff(day,A.dt,B.[时间])=0
group by A.dt--结果
/*
dt                                                     数量          
------------------------------------------------------ ----------- 
2005-09-07 00:00:00.000                                86
2005-09-08 00:00:00.000                                0
2005-09-09 00:00:00.000                                33
2005-09-10 00:00:00.000                                0
2005-09-11 00:00:00.000                                22
2005-09-12 00:00:00.000                                0
2005-09-13 00:00:00.000                                0
2005-09-14 00:00:00.000                                13（所影响的行数为 8 行）
*/

Answer 4

create table t (时间 datetime,数量 int)
insert t select '2005-9-7', 44
insert t select '2005-9-9',33
insert t select '2005-9-11',22
insert t select '2005-9-14',33  
select * from t
declare @d datetime
set @d=cast('2005-9-8' as datetime)
while @d<cast('2005.09.14' as datetime)
begin
 insert t select top 1 @d,0 from t where 时间<>cast(@d as datetime)
 set @d=dateadd(day,2,@d)
end

Answer 5

--写错了,改一下
--测试数据
declare @t table (时间 datetime,数量 int)
insert @t select '2005-9-7', 44
insert @t select '2005-9-9',33
insert @t select '2005-9-11',22
insert @t select '2005-9-14',13  
declare @d datetime
set @d='2005-9-7'
while @d<'2005.09.14'
begin
 insert into @t select top 1 @d,0 from @t where @d not in(select 时间 from @t)
 set @d=dateadd(day,1,@d)
endselect * from @t order by 时间--结果时间                                                     数量          
------------------------------------------------------ ----------- 
2005-09-07 00:00:00.000                                44
2005-09-08 00:00:00.000                                0
2005-09-09 00:00:00.000                                33
2005-09-10 00:00:00.000                                0
2005-09-11 00:00:00.000                                22
2005-09-12 00:00:00.000                                0
2005-09-13 00:00:00.000                                0
2005-09-14 00:00:00.000                                13（所影响的行数为 8 行）--删除测试数据
drop table t
go