有如下一个表a,内容如下
JZID CJSJ YCWGSWD YCWHSWD
---------- --------------------------- -------------------- --------------------
JZ01010101 2007-10-12 13:50:00.000 39.30 49.30
JZ01010101 2007-10-12 13:51:00.000 45.15 39.62
JZ01010101 2007-10-12 13:52:00.000 50.48 59.96
JZ01010101 2007-10-12 13:53:00.000 59.35 30.92
JZ01010101 2007-10-12 13:54:00.000 64.72 28.80
JZ01010101 2007-10-12 13:55:00.000 72.58 35.64
JZ01010101 2007-10-12 13:56:00.000 25.44 20.10
JZ01010101 2007-10-12 13:57:00.000 30.31 39.49
JZ01010101 2007-10-12 13:58:00.000 35.61 50.99
JZ01010101 2007-10-12 13:59:00.000 44.51 30.79
要求: 得到1分钟或者3又或者5分钟之内的平均值;
例如:3分钟的结果如下:
JZID CJSJ YCWGSWD YCWHSWD
---------- --------------------------- -------------------- --------------------
JZ01010101 2007-10-12 13:52:00.000 44.98 49.63
JZ01010101 2007-10-12 13:55:00.000 65.55 31.79
.
.
.
其中的3分钟是不固定,可以根据需要选择.如何写这样的语句呢?
------解决方案--------------------
- SQL code
declare @a table(JZID varchar(10),CJSJ datetime,YCWGSWD decimal(10,2),YCWHSWD decimal(10,2))insert @a select 'JZ01010101','2007-10-12 13:50:00.000',39.30,49.30 union all select 'JZ01010101' ,'2007-10-12 13:51:00.000', 45.15, 39.62 union all select 'JZ01010101' ,'2007-10-12 13:52:00.000', 50.48, 59.96 union all select 'JZ01010101' ,'2007-10-12 13:53:00.000', 59.35, 30.92 union all select 'JZ01010101' ,'2007-10-12 13:54:00.000', 64.72, 28.80 union all select 'JZ01010101' ,'2007-10-12 13:55:00.000', 72.58, 35.64 union all select 'JZ01010101' ,'2007-10-12 13:56:00.000', 25.44, 20.10 union all select 'JZ01010101' ,'2007-10-12 13:57:00.000', 30.31, 39.49 union all select 'JZ01010101' ,'2007-10-12 13:58:00.000', 35.61, 50.99 union all select 'JZ01010101' ,'2007-10-12 13:59:00.000', 44.51, 30.79 declare @minute intset @minute=3select JZID,max(CJSJ) CJSJ,avg(YCWGSWD)YCWGSWD,avg(YCWHSWD) YCWHSWD from(select *,ceiling((datediff(minute,(select min(cjsj) from @a),cjsj)+1)*1.0 [email protected]) xx from @a) aagroup by jzid,xx--result/*JZID CJSJ YCWGSWD YCWHSWD ---------- ------------------------------------------------------ ---------------------------------------- ---------------------------------------- JZ01010101 2007-10-12 13:52:00.000 44.976666 49.626666JZ01010101 2007-10-12 13:55:00.000 65.550000 31.786666JZ01010101 2007-10-12 13:58:00.000 30.453333 36.860000JZ01010101 2007-10-12 13:59:00.000 44.510000 30.790000（所影响的行数为 4 行）*/