SQL查询每小时，每天，每月的平均值
SQL查询每小时，每天，每月的平均值：
有这么一个Table表：
timeid v001 v002 v003 v004
2008-01-01 01:05:00 1 2 3 4
2008-01-01 01:25:00 1 2 3 4
2008-01-01 01:55:00 1 2 3 4
2008-01-01 02:05:00 1 2 3 4
2008-01-01 02:25:00 1 2 3 4
2008-01-01 02:55:00 1 2 3 4
..
..
..
2008-01-01 23:55:00 1 2 3 4
2008-01-02 01:05:00 1 2 3 4
..
..
2008-01-02 23:55:00 1 2 3 4
...
...
...
2008-01-31 23:55:00 1 2 3 4
每小时平均值查询指的是：如2008-01-01 01:05:00-----2008-01-01 01:55:00之间的值的平均
每天平均值查询指的是：如2008-01-01 01:05:00-----2008-01-01 23:55:00之间的值的平均
每月平均值查询指的是：如2008-01-01 01:05:00-----2008-01-31 23:55:00之间的值的平均
timeid 是samlltimedate
[解决办法]
--#1.每小时平均值
select year(timeid), MONTH(timeid), DAY(timeid), DATEPART(hour, timeid), AVG(v001), AVG(v002), AVG(v003), AVG(v004)
from tb
group by year(timeid), MONTH(timeid), DAY(timeid), DATEPART(hour, timeid)
--#2.每天平均值
select year(timeid), MONTH(timeid), DAY(timeid), AVG(v001), AVG(v002), AVG(v003), AVG(v004)
from tb
group by year(timeid), MONTH(timeid), DAY(timeid)
--#3.每月平均值
select year(timeid), MONTH(timeid), AVG(v001), AVG(v002), AVG(v003), AVG(v004)
from tb
group by year(timeid), MONTH(timeid)
获取两个时间之内的所有日期
--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2008-10-15 00:00:000'
set @edate = '2009-02-10 00:00:000'
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
--sql 2005
declare @startDate datetime
declare @endDate datetime
SELECT @startDate = '2008-10-15' ,@endDate = '2009-01-23'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date from tb
