我有一个表字段为counts int,logtime datatime
counts logtime
1 2010-3-15 0:00:00
2 2010-3-15 1:00:00
3 2010-3-15 2:00:00
4 2010-3-16 0:00:00
5 2010-3-16 1:00:00
6 2010-3-16 2:00:00
7 2010-3-17 0:00:00
8 2010-3-17 3:00:00
9 2010-3-17 4:00:00
要求按天分组,得出logtime和counts平均值
如上数据需要得到结果
2 2010-3-15
5 2010-3-16
8 2010-3-17
求解
counts logtime
1 2010-3-15 0:00:00
2 2010-3-15 1:00:00
3 2010-3-15 2:00:00
4 2010-3-16 0:00:00
5 2010-3-16 1:00:00
6 2010-3-16 2:00:00
7 2010-3-17 0:00:00
8 2010-3-17 3:00:00
9 2010-3-17 4:00:00
要求按天分组,得出logtime和counts平均值
如上数据需要得到结果
2 2010-3-15
5 2010-3-16
8 2010-3-17
求解
from tb
group by convert(varchar(10),logtime,120)
如果一组是偶数个counts怎么办
create table #(counts int, logtime datetime)
insert # select 1 ,'2010-3-15 0:00:00'
insert # select 2 ,'2010-3-15 1:00:00'
insert # select 3 ,'2010-3-15 2:00:00'
insert # select 4 ,'2010-3-16 0:00:00'
insert # select 5 ,'2010-3-16 1:00:00'
insert # select 6 ,'2010-3-16 2:00:00'
insert # select 7 ,'2010-3-17 0:00:00'
insert # select 8 ,'2010-3-17 3:00:00'
insert # select 9 ,'2010-3-17 4:00:00'select avg(counts)as cnt,convert(char(10),logtime,120) as dt from #
group by convert(char(10),logtime,120) cnt dt
----------- ----------
2 2010-03-15
5 2010-03-16
8 2010-03-17(3 行受影响)
insert # select 1 ,'2010-3-15 0:00:00'
insert # select 2 ,'2010-3-15 1:00:00'
insert # select 3 ,'2010-3-15 2:00:00'
insert # select 4 ,'2010-3-16 0:00:00'
insert # select 5 ,'2010-3-16 1:00:00'
insert # select 6 ,'2010-3-16 2:00:00'
insert # select 7 ,'2010-3-17 0:00:00'
insert # select 8 ,'2010-3-17 3:00:00'
insert # select 9 ,'2010-3-17 4:00:00'select avg(counts)as cnt,convert(char(10),logtime,120) as dt from #
group by convert(char(10),logtime,120)
create table #t(counts int, logtime datetime)
insert #t select 1 ,'2010-3-15 0:00:00'
insert #t select 2 ,'2010-3-15 1:00:00'
insert #t select 3 ,'2010-3-15 2:00:00'
insert #t select 4 ,'2010-3-16 0:00:00'
insert #t select 5 ,'2010-3-16 1:00:00'
insert #t select 6 ,'2010-3-16 2:00:00'
insert #t select 7 ,'2010-3-17 0:00:00'
insert #t select 8 ,'2010-3-17 3:00:00'
insert #t select 9 ,'2010-3-17 4:00:00'
select convert(char(10),logtime,120) logtime,avg(counts) counts
from #t
group by convert(char(10),logtime,120)
convert(varchar(10),logtime,120) as logtime,avg(counts) as avgCounts
from
tb
group by
convert(varchar(10),logtime,120)