没有办法了,求一语句,该如何处理-MSSQL教程-爱易网页

没有办法了,求一语句,该如何处理

日期：2014-05-18　浏览次数：20446 次

没有办法了,求一语句
有如下一个表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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

没有办法了,求一语句,该如何处理

相关资料更多>

推荐阅读更多>