项目中经常碰到需要在数据表行中取最大值、最小值、平均值问题,SQL数据库不支持此功能,所以我们需要转换一下。比如说有以下表结构:
图1数据库表结构
我们需要选取出每个时间点(DateTime)每个设备(PositionID)的所有数值(Value1,Value2,Value3,Value4)平均值、最大值、最小值,可采用如下方法:
select t.[datetime] as [DateTime], t.positionid as Positionid,
max(t.value1) as MaxValue, min(t.value1) as MinValue, avg(t.value1) as AvgValue
from
(select [datetime], positionid, value1 from historydata union
select [datetime], positionid, value2 from historydata union
select [datetime], positionid, value3 from historydata union
select [datetime], positionid, value4 from historydata) t
where positionid in
(
select positionid
from 表
)
group by t.[datetime], [positionid]
order by positionid, [datetime]