用view其實就很好解決了,如果要用存儲過程的話自己再封裝一下 IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test') DROP TABLE test go CREATE TABLE test ( dt DATETIME , num DECIMAL(6,4) ) GOINSERT INTO test (dt , num) SELECT '2001-08-21 00:00:00.000' , 1.529 UNION ALL SELECT '2001-08-21 01:00:00.000' , 1.524 UNION ALL SELECT '2001-08-21 02:00:00.000' , 1.522 UNION ALL SELECT '2001-08-21 03:00:00.000' , 1.52 UNION ALL SELECT '2001-08-21 04:00:00.000' , 1.522 UNION ALL SELECT '2001-08-21 05:00:00.000' , 1.526 UNION ALL SELECT '2001-08-21 06:00:00.000' , 1.532 UNION ALL SELECT '2001-08-21 07:00:00.000' , 1.536 UNION ALL SELECT '2001-08-21 08:00:00.000' , 1.539 UNION ALL SELECT '2001-08-21 09:00:00.000' , 1.541 UNION ALL SELECT '2001-08-21 10:00:00.000' , 1.539 UNION ALL SELECT '2001-08-21 11:00:00.000' , 1.536 UNION ALL SELECT '2001-08-21 12:00:00.000' , 1.533 UNION ALL SELECT '2001-08-21 13:00:00.000' , 1.529 UNION ALL SELECT '2001-08-21 14:00:00.000' , 1.526 ---------------------------------執行查詢---------------------- ;WITH a AS( SELECT CONVERT(DATE , dt) AS yyyymmdd , DATEPART(HOUR , dt)/6 AS t ,num FROM test) SELECT yyyymmdd , t , AVG(num) AS avg_num FROM a GROUP BY yyyymmdd , t /* yyyymmdd t avg_num ---------- ----------- --------------------------------------- 2001-08-21 0 1.523833 2001-08-21 1 1.537166 2001-08-21 2 1.529333(3 row(s) affected) */
楼主完全可以在查询的时候把这样的逻辑写出来,不用存储过程也可以的。insert into tb select .... from newtb where ... group by datediff(hh,getdate(),date)/6
IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test')
DROP TABLE test
go
CREATE TABLE test
(
dt DATETIME ,
num DECIMAL(6,4)
)
GOINSERT INTO test (dt , num)
SELECT '2001-08-21 00:00:00.000' , 1.529 UNION ALL
SELECT '2001-08-21 01:00:00.000' , 1.524 UNION ALL
SELECT '2001-08-21 02:00:00.000' , 1.522 UNION ALL
SELECT '2001-08-21 03:00:00.000' , 1.52 UNION ALL
SELECT '2001-08-21 04:00:00.000' , 1.522 UNION ALL
SELECT '2001-08-21 05:00:00.000' , 1.526 UNION ALL
SELECT '2001-08-21 06:00:00.000' , 1.532 UNION ALL
SELECT '2001-08-21 07:00:00.000' , 1.536 UNION ALL
SELECT '2001-08-21 08:00:00.000' , 1.539 UNION ALL
SELECT '2001-08-21 09:00:00.000' , 1.541 UNION ALL
SELECT '2001-08-21 10:00:00.000' , 1.539 UNION ALL
SELECT '2001-08-21 11:00:00.000' , 1.536 UNION ALL
SELECT '2001-08-21 12:00:00.000' , 1.533 UNION ALL
SELECT '2001-08-21 13:00:00.000' , 1.529 UNION ALL
SELECT '2001-08-21 14:00:00.000' , 1.526
---------------------------------執行查詢----------------------
;WITH a AS(
SELECT CONVERT(DATE , dt) AS yyyymmdd , DATEPART(HOUR , dt)/6 AS t ,num
FROM test)
SELECT yyyymmdd , t , AVG(num) AS avg_num
FROM a
GROUP BY yyyymmdd , t
/*
yyyymmdd t avg_num
---------- ----------- ---------------------------------------
2001-08-21 0 1.523833
2001-08-21 1 1.537166
2001-08-21 2 1.529333(3 row(s) affected)
*/
select ....
from newtb
where ...
group by datediff(hh,getdate(),date)/6
如果是一个分钟表,要每小时计算一个平均值呢?