[求助]急！按给定时间范围，分别求出小时、日、月均值

[ShadowDust]

表如下：
（有两列，数据列是float，记录时间是smalldatetime）

data time
0.123456 2006-03-19 08：55：00
....... ....................
0.654321 2007-03-19 18：56：00
....... ...................

给定一个时间范围，比如2006-10-01 00：00：00 到2007-01-01 00：00：00
求sql语句，可以求出每小时数据均值，每天数据均值，每月均值。
得到的结果要data和time字段都有。

在线等高手指点，马上给分。

[paoluo]

--每小时数据均值
Select
Convert(Varchar(13), 记录时间, 120) As 记录时间,
AVG(数据) As 每小时数据均值
From
TableName
Group By Convert(Varchar(13), 记录时间, 120)

--每天数据均值
Select
Convert(Varchar(10), 记录时间, 120) As 记录时间,
AVG(数据) As 每天数据均值
From
TableName
Group By Convert(Varchar(10), 记录时间, 120)

--每月均值
Select
Convert(Varchar(7), 记录时间, 120) As 记录时间,
AVG(数据) As 每月均值
From
TableName
Group By Convert(Varchar(7), 记录时间, 120)





---------

這個不行？

[paoluo]

--每小时数据均值
Select
Convert(Varchar(13), [time], 120) As [time],
AVG(data) As 每小时数据均值
From
TableName
Group By Convert(Varchar(13), [time], 120)

--每天数据均值
Select
Convert(Varchar(10), [time], 120) As [time],
AVG(data) As 每天数据均值
From
TableName
Group By Convert(Varchar(10), [time], 120)

--每月均值
Select
Convert(Varchar(7), [time], 120) As [time],
AVG(data) As 每月均值
From
TableName
Group By Convert(Varchar(7), [time], 120)

[dawugui]

select right(convert(varchar(13),time,120),2) as 每小时 , avg(data) 每小时数据均值 from tb group by right(substring(varchar(13),time,120),2)
select convert(varchar(10),time,120) as 每天 , avg(data) 每天数据均值 from tb group by convert(varchar(10),time,120)
select convert(varchar(7),time,120) as 每月 , avg(data) 每月数据均值 from tb group by convert(varchar(7),time,120)

[子陌红尘]

--按小时求平均：
select
avg(data) as data,
convert(varchar(13),time,120) as time
from
表
group by
convert(varchar(13),time,120)

--按日求平均：
select
avg(data) as data,
convert(varchar(10),time,120) as time
from
表
group by
convert(varchar(10),time,120)

--按月求平均：
select
avg(data) as data,
convert(varchar(7),time,120) as time
from
表
group by
convert(varchar(7),time,120)

[ShadowDust]

可以得到均值的，但我不会在给定的时间范围中查询，呵呵，大大帮一下阿

[dawugui]

select right(convert(varchar(13),time,120),2) as 每小时 , avg(data) 每小时数据均值 from tb where time >= '2006-10-01 00:00:00' and time <= '2007-01-01 23:59:59' group by right(substring(varchar(13),time,120),2)
select convert(varchar(10),time,120) as 每天 , avg(data) 每天数据均值 from tb where time >= '2006-10-01 00:00:00' and time <= '2007-01-01 23:59:59' group by convert(varchar(10),time,120)
select convert(varchar(7),time,120) as 每月 , avg(data) 每月数据均值 from tb where time >= '2006-10-01 00:00:00' and time <= '2007-01-01 23:59:59' group by convert(varchar(7),time,120)

[dawugui]

把条件加在后面

[paoluo]

ShadowDust() ( ) 信誉：100 Blog 2007-03-19 16:08:09 得分: 0


可以得到均值的，但我不会在给定的时间范围中查询，呵呵，大大帮一下阿

------------------------------------------------

--每小时数据均值
Select
Convert(Varchar(13), [time], 120) As [time],
AVG(data) As 每小时数据均值
From
TableName
Where [time] Between '2006-10-01 00:00:00' And '2007-01-01 00:00:00'
Group By Convert(Varchar(13), [time], 120)

--每天数据均值
Select
Convert(Varchar(10), [time], 120) As [time],
AVG(data) As 每天数据均值
From
TableName
Where [time] Between '2006-10-01 00:00:00' And '2007-01-01 00:00:00'
Group By Convert(Varchar(10), [time], 120)

--每月均值
Select
Convert(Varchar(7), [time], 120) As [time],
AVG(data) As 每月均值
From
TableName
Where [time] Between '2006-10-01 00:00:00' And '2007-01-01 00:00:00'
Group By Convert(Varchar(7), [time], 120)

[ShadowDust]

比如，仅仅需要查刚刚过去的一个小时的均值，怎么写？
我实在是着急要改个东西，大家先帮我一下，以后我会认真学sql的。

[ShadowDust]

呵呵，谢谢大家。

[paoluo]

ShadowDust() ( ) 信誉：100 Blog 2007-03-19 16:11:46 得分: 0


比如，仅仅需要查刚刚过去的一个小时的均值，怎么写？
我实在是着急要改个东西，大家先帮我一下，以后我会认真学sql的。


-------------------
Select
Convert(Varchar(13), [time], 120) As [time],
AVG(data) As 每小时数据均值
From
TableName
Where DateDiff(hh, [time], GetDate()) = 1
Group By Convert(Varchar(13), [time], 120)