select
RoomName,
Elect,
WaterHot,
WaterCool,
Sum(InfraredRoom) as 'InfraredRoom',
Sum(InfraredToilet) as 'InfraredToilet',
Sum(DoorState as int) as 'DoorState'
from (select room.RoomName as 'RoomName',
round(Max(ZDL)-Min(ZDL),1) as 'Elect',
Max(RSB)-Min(RSB) as 'WaterHot',
Max(LSB)-Min(LSB) as 'WaterCool',
(InfraredRoom) as 'InfraredRoom',
(InfraredToilet) as 'InfraredToilet',
(Cast(DoorState as int)) as 'DoorState'
From Gamar_RoomSet as room
left join
Gamar_GetHappend AS comm
on room.RoomID=comm.RoomID
And comm.SBRQ between @StartTime and @EndTime
left join Gamar_GetHappendForWink as wink
on room.RoomID=wink.RoomID
And wink.SJRQ between @StartTime and @EndTime
Group by room.RoomName
order by room.RoomName
) T
group by RoomName,
Elect,
WaterHot,
WaterCool
这样行不?
RoomName,
Elect,
WaterHot,
WaterCool,
Sum(InfraredRoom) as 'InfraredRoom',
Sum(InfraredToilet) as 'InfraredToilet',
Sum(DoorState as int) as 'DoorState'
from (select room.RoomName as 'RoomName',
round(Max(ZDL)-Min(ZDL),1) as 'Elect',
Max(RSB)-Min(RSB) as 'WaterHot',
Max(LSB)-Min(LSB) as 'WaterCool',
(InfraredRoom) as 'InfraredRoom',
(InfraredToilet) as 'InfraredToilet',
(Cast(DoorState as int)) as 'DoorState'
From Gamar_RoomSet as room
left join
Gamar_GetHappend AS comm
on room.RoomID=comm.RoomID
And comm.SBRQ between @StartTime and @EndTime
left join Gamar_GetHappendForWink as wink
on room.RoomID=wink.RoomID
And wink.SJRQ between @StartTime and @EndTime
Group by room.RoomName
order by room.RoomName
) T
group by RoomName,
Elect,
WaterHot,
WaterCool
这样行不?
select room.RoomName as 'RoomName',
round(Max(ZDL)-Min(ZDL),1) as 'Elect',
Max(RSB)-Min(RSB) as 'WaterHot',
Max(LSB)-Min(LSB) as 'WaterCool',
(select Sum(InfraredRoom) from 该字段对应的表) as 'InfraredRoom',
(select Sum(InfraredToilet)from 该字段对应的表) as 'InfraredToilet',
(select Sum(Cast(DoorState as int)) from 该字段对应的表) as 'DoorState'
From Gamar_RoomSet as room
left join
Gamar_GetHappend AS comm
on room.RoomID=comm.RoomID
And comm.SBRQ between @StartTime and @EndTime
left join Gamar_GetHappendForWink as wink
on room.RoomID=wink.RoomID
And wink.SJRQ between @StartTime and @EndTime
Group by room.RoomName
order by room.RoomName
3楼
SUM(列) 是计算这个列的值总和 我清楚哈
但多表分组查询后 SUM(列)的结果 是 (列值的总和 再乘以 说包含结果行数)
也就是说如果GROUP 分组后说匹配的行数 * SUM(列) 是个交叉乘法
但我想要就是SUM的真实结果 并非他乘以匹配行数的结果
这个查询应该怎么改??
哪个表需要SUM的分开先SUM吧!!!
连接后SUM肯定会不对!!