当前位置: 代码迷 >> Sql Server >> ,求平均数高效sql语句
  详细解决方案

,求平均数高效sql语句

热度:63   发布时间:2016-04-27 11:49:50.0
在线等,求平均数高效sql语句
create table t_AvgDemo
(
  DevId int,
  DevName nvarchar(20),
  DataType nvarchar(20),
  RealData float,
  CurrentTime datetime,
  constraint[PK_AvgDemo_DC] primary key clustered(DevId,CurrentTime)
)
go

insert into t_AvgDemo values(1,'蓄电池A','电流A',110.2,'2012-07-23 08:20:05')
insert into t_AvgDemo values(1,'蓄电池A','电流A',111.5,'2012-07-23 08:20:28')
insert into t_AvgDemo values(1,'蓄电池A','电流A',112.3,'2012-07-23 08:20:56')
insert into t_AvgDemo values(1,'蓄电池A','电流A',109.2,'2012-07-23 08:21:05')
insert into t_AvgDemo values(1,'蓄电池A','电流A',108.3,'2012-07-23 08:21:35')
insert into t_AvgDemo values(1,'蓄电池A','电流A',110.9,'2012-07-23 08:21:50')

insert into t_AvgDemo values(1,'蓄电池A','电流B',113.2,'2012-07-23 08:20:10')
insert into t_AvgDemo values(1,'蓄电池A','电流B',115.5,'2012-07-23 08:20:15')
insert into t_AvgDemo values(1,'蓄电池A','电流B',114.3,'2012-07-23 08:20:30')
insert into t_AvgDemo values(1,'蓄电池A','电流B',109.2,'2012-07-23 08:21:10')
insert into t_AvgDemo values(1,'蓄电池A','电流B',110.3,'2012-07-23 08:21:40')
insert into t_AvgDemo values(1,'蓄电池A','电流B',114.9,'2012-07-23 08:21:55')

--要求为:
-- DevId DevName DataType RealData CurrentTime
-- 1 蓄电池A 电流A 111.3 2012-07-23 08:20:00
-- 1 蓄电池A 电流A 109.5 2012-07-23 08:21:00
-- 1 蓄电池B 电流B 114.3 2012-07-23 08:20:00
-- 1 蓄电池B 电流B 111.5 2012-07-23 08:21:00

--求解 ?

------解决方案--------------------
SELECT DevId ,DevName, DataType,avg( RealData )RealData ,convert(char(17),CurrentTime,120)+'00'
from tb
group by
 DevId ,DevName, DataType,convert(char(17),CurrentTime,120)+'00'

------解决方案--------------------
SQL code
SELECT  DevId,DevName,DataType,ROUND(AVG(RealData),1),LEFT(CurrentTime,17) + '00'FROM t_AvgDemoGROUP BY DevId,DevName,DataType,LEFT(CurrentTime,17)
  相关解决方案