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

答案:6 悬赏:40 手机版

解决时间 2021-12-17 22:43

提问者网友：遮云壑
2021-12-17 14:33

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这样的结果

最佳答案

五星知识达人网友：长青诗
2022-01-05 11:48

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

全部回答

1楼网友：往事埋风中
2022-01-05 16:53

上面回答的都对

2楼网友：独行浪子会拥风
2022-01-05 15:37

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

3楼网友：一叶十三刺
2022-01-05 14:25

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

4楼网友：人類模型
2022-01-05 13:26

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

5楼网友：空山清雨
2022-01-05 12:12

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

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息