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)