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

create table test(a int,
time date)
-----------------
insert into test values(2,2011-08-10);
insert into test values(1,2011-08-10);
insert into test values(3,2011-08-11);
insert into test values(5,2011-08-13);

要搜出
time a
-----------------------------
2011-8-10 3
2011-8-11 3
2011-8-12 0
2011-8-13 5这样的结果

可以使用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 date, count(id) as c from test group by date

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

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,日期) ) 将表和日期改为相应的表明和日期列名即可