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

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

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

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

相关资料更多>

  1. 刚刚接触数据库 求教一道题
  2. 这个sql话语为什么执行了两次
  3. 记录合并的有关问题 来者有分
  4. 各位帮忙啊解决思路
  5. Null的学识
  6. 关于大类和二类的联合查询，部分大类下二类为空怎么处理
  7. sql导出EXCEL语句语法异常
  8. SQL SERVER的function有关问题
  9. 求sql语句?该如何处理