sql按时间统计

Question

表CountByTime结构如下
ID        CreateTime             K      D       Y
1 1900-01-01 00:00:00.000  1 2 3
2 1900-01-01 00:05:00.000  2 3 4
3 1900-01-01 01:05:00.000  2 3 6
4 1900-01-01 02:05:00.000  5 6 2其中 ID是自增长列现在按小时统计要求查询后结果如下
  CreateTime             K      D       Y
1900-01-01 00:00:00.000  3 5 7
1900-01-01 01:00:00.000  2 3 6
1900-01-01 02:00:00.000  5 6 2其中
1 1900-01-01 00:00:00.000  1 2 3
2 1900-01-01 00:05:00.000  2 3 4
这两条数据都属于0点的所以要合并求高手！！！

Answer 1

问题重新整理下
表CountByTime结构如下
ID CreateTime             Kvalue Dvalue Yvalue
1 1900-01-01 00:00:00.000 ww       ew      gd
2 1900-01-01 00:05:00.000 rr       gd      sf
3 1900-01-01 01:05:00.000 ss       gd      asd
4 1900-01-01 02:05:00.000 gg       ew      sd其中 ID是自增长列现在按小时统计要求查询后结果如下
  CreateTime            CountK CountD CountY
1900-01-01 00:00:00.000   2      2      2
1900-01-01 01:00:00.000   1      1      1
1900-01-01 02:00:00.000   1      1      1其中
1 1900-01-01 00:00:00.000 ww       ew      gd
2 1900-01-01 00:05:00.000 rr       gd      sf
这两条数据都属于0点的所以要合并

Answer 2

select convert(varchar(13),min(CreateTime),112)+':00:00.000' as CreateTime,
sum(K) as K,
sum(D) as D,
sum(Y) as Y
from CountByTime
group by convert(varchar(13),min(CreateTime),112),datepart(hh,CreateTime)

Answer 3

select  CreateTime, sum(K) K,
sum( D) D,
sum( Y) Y
 from CountByTime
 group  by   DATEPART(YYYY,[CreateTime])
  ,DATEPART(M ,[CreateTime])
 ,DATEPART(D ,[CreateTime])
 ,DATEPART(HH,[CreateTime])

Answer 4

select convert(varchar(13),min(CreateTime),121)+':00:00.000' as CreateTime,
sum(K) as K,
sum(D) as D,
sum(Y) as Y
from CountByTime
group by convert(varchar(13),min(CreateTime),121),datepart(hh,CreateTime)

Answer 5

select convert(varchar(13),min(CreateTime),121)+':00:00.000' as CreateTime,
sum(K) as K,
sum(D) as D,
sum(Y) as Y
from CountByTime
group by convert(varchar(13),CreateTime,121),datepart(hh,CreateTime)

Answer 6

create table tb
(
ID int,
CreateTime datetime,
K int,
D int,
Y int
)
insert into tb values(1, '1900-01-01 00:00:00.000', 1, 2, 3)
insert into tb values(2, '1900-01-01 00:05:00.000', 2, 3, 4)
insert into tb values(3, '1900-01-01 01:05:00.000', 2, 3, 6)
insert into tb values(4, '1900-01-01 02:05:00.000', 5, 6, 2)
select convert(varchar(13),CreateTime,120)+':00:00.000' CreateTime,sum(K) K,sum(D) D, sum(Y) Y from tb
group by convert(varchar(13),CreateTime,120)
/*
CreateTime,K,D,Y
1900-01-01 00:00:00.000,3,5,7
1900-01-01 01:00:00.000,2,3,6
1900-01-01 02:00:00.000,5,6,2(3 行受影响)

Answer 7

select
 convert(varchar(13),min(CreateTime),112)+':00:00.000' as CreateTime,
 sum(K) as K,
 sum(D) as D,
 sum(Y) as Y
from
 CountByTime
group by
 convert(varchar(13),min(CreateTime),112),datepart(hh,CreateTime)