Question

sql server 如何用一条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这样的结果
分组语句 以及组函数我都知道，主要告诉我如何在select语句中补全日期

Answer 1

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下楼上两位误人子弟的，写的语句都不通吧

Answer 2

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

Answer 3

可以使用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)

Answer 4

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

Answer 5

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