22,209
社区成员
发帖
与我相关
我的任务
分享
declare @Bdate smalldatetime,@Edate smalldatetime,@i int
select @Bdate='2009-01-01',@Edate='2009-01-03',@i=datediff(dd,@Bdate,@Edate)
declare @c table([date] smalldatetime,cnt int)
insert @c
select '2009-01-01',20 union all
select '2009-01-01',1 union all
select '2009-01-02',10
declare @date table(date smalldatetime)
while(@i>=0)
begin
insert @date
select dateadd(dd,0-@i,@Edate)
select @i=@i-1
end
select a.[date],cnt=sum(isnull(cnt,0))
from @date a left join @c b
on a.date=b.date
group by a.date
date cnt
----------------------- -----------
2009-01-01 00:00:00 21
2009-01-02 00:00:00 10
2009-01-03 00:00:00 0
(3 行受影响)
declare @be datetime,@en datetime
set @be='2009-1-1'
set @en='2009-1-3'
select A.[date],sum(case when 某栏位 is null then 0 else 1 end) as [cnt]
from
(
select distinct dateadd(day,number,@be) as [date]
from master.dbo.spt_values
where number between 0 and datediff(day,@be,@en)
) A
left join 表名 B
on A.[date]=B.[date]
group by A.[date]
create table test ( a datetime)
declare @num int
set @num = 1
while @num < 1000
begin
insert into test values(convert(varchar(10),getdate()-@num,120)
set @num = @num - 1
end