没有办法了,求一语句,该如何处理
日期：2014-05-18 浏览次数：20604 次
没有办法了,求一语句
有如下一个表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分钟是不固定,可以根据需要选择.如何写这样的语句呢?
------解决方案--------------------
有如下一个表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 int
set @minute=3
select JZID,max(CJSJ) CJSJ,avg(YCWGSWD)YCWGSWD,avg(YCWHSWD) YCWHSWD from
(select *,ceiling((datediff(minute,(select min(cjsj) from @a),cjsj)+1)*1.0 /@minute) xx from @a) aa
group by jzid,xx
--result
/*
JZID CJSJ YCWGSWD YCWHSWD
---------- ------------------------------------------------------ ---------------------------------------- ----------------------------------------
JZ01010101 2007-10-12 13:52:00.000 44.976666 49.626666
JZ01010101 2007-10-12 13:55:00.000 65.550000 31.786666
JZ01010101 2007-10-12 13:58:00.000 30.453333 36.860000
JZ01010101 2007-10-12 13:59:00.000 44.510000 30.7900
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
