sql server 如何用一条select语句查出表中一段时间每天的某数据之和 （并且补全缺少的日期）主要

declare @temp table([time] date ,a int)
insert into @temp select t.[time],SUM(t.a) as a from Test t group by t.time order by t.[time]
declare dateList Cursor for select [time] From @temp
open dateList
declare @day date, @nextDay date, @addDay date, @count int, @index int;
set @count = (select COUNT(*) from @temp)
set @index = 0;
Fetch Next From dateList into @day
while (@index < @count)
begin
Fetch Next From dateList into @nextDay
set @addDay = DATEADD(DAY,1,@day)
while(@addDay < @nextDay)
begin
insert into @temp values(@addDay,0);set @addDay=DATEADD(DAY,1,@addDay);
end
set @day=@nextDay
set @index = @index + 1
end
close dateList
Deallocate dateList
select * from @temp order by [time]

楼主自己测试下看对不对把，运行没问题， 结果不对的话再问我把， 顺便bs下楼上两位误人子弟的，写的语句都不通吧

select COUNT(*),convert(date,max(日期))
from 表 group by convert(date,日期)
union all
select * from
(select 0 co,convert(varchar(10),dateadd(dd,number,convert(varchar(8),getdate(),120)+'01'),120) as dt
from master..spt_values
where type='P'
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')) c
where dt not in (select convert(date,max(日期)) from 表 group by convert(date,日期) )
将表和日期改为相应的表明和日期列名即可

可以使用convert函数。

如某表的日期字段为datetime

如表为test，日期字段为statedate，求和字段为grade。

sql语句：

select convert(varchar(10),statedate,120) statedate,sum(grade) grade from test where convert(varchar(10),statedate,120) between '2015-06-01' and '2015-06-30' group by convert(varchar(10),statedate,120)

select sum(a) as a,convert(varchar(7),date,120) as date
from test group by prokey,convert(varchar(7),date,120)

select date, count(id) as c
from test
group by date