如何根据日期时间点归并

[sdluhaibing]

现在我有如下的表数据
Date Value
2016-03-23 00:05:00.000 245833.0152000000
2016-03-23 00:10:00.000 150117.9056000000
2016-03-23 00:15:00.000 279134.5132500000
2016-03-23 00:20:00.000 146963.7195700000
2016-03-23 00:25:00.000 152732.6481400000
2016-03-23 00:30:00.000 136986.6713100000
2016-03-23 00:35:00.000 340922.0038000000
2016-03-23 00:40:00.000 263542.3197700000
2016-03-23 00:45:00.000 218160.9406900000
2016-03-23 00:50:00.000 150001.5314700000

数据是一个月内，3月1号到31号，每天每隔五分钟有一条记录。

现在想得到这样的表，
第一列是时间点（从00:05:00.000到第二天的00:00:00.000）
第二列是这一个时间点 一个月内 value的平均值


求大虾帮忙

[spiritofdragon]

with t(Date,Value) as (
select cast('2016-03-23 00:05:00.000' as datetime),245833.0152000000union all
select '2016-03-23 00:10:00.000',150117.9056000000union all
select '2016-03-23 00:15:00.000',279134.5132500000union all
select '2016-03-23 00:20:00.000',146963.7195700000union all
select '2016-03-23 00:25:00.000',152732.6481400000union all
select '2016-03-23 00:30:00.000',136986.6713100000union all
select '2016-03-23 00:35:00.000',340922.0038000000union all
select '2016-03-23 00:40:00.000',263542.3197700000union all
select '2016-03-23 00:45:00.000',218160.9406900000union all
select '2016-03-23 00:50:00.000',150001.5314700000union all
select '2016-03-24 00:05:00.000',111111.0152000000union all
select '2016-03-24 00:10:00.000',222222.9056000000
)
select CONVERT(varchar(8),date,108) ,AVG(Value)
from t
group by CONVERT(varchar(8),date,108)

[shadowpj]

第一列是时间点（从00:05:00.000到第二天的00:00:00.000） 那么日期应该是条件吧？要指定一天 select convert(varchar(8),date,108) as 时间点,avg(value) as 平均值 from 表 where date>='2016-03-23 00:05:00' and date<='2016-03-23 23:59:59' group by convert(varchar(8),date,108)

[寡丶人]

1、我建立一个模拟表： 2、sql语句：

select  CONVERT(CHAR(8), date, 8 ) as 时间段,  
        sum(value) as 平均值   
from  test_table  
group by  CONVERT(CHAR(8), date, 8 )

3、执行结果：

[寡丶人]

我把avg写成sum。