select count ( distinct macid)as Maccount , [ControlTime],sum (case when ControResult=1 then 1 else 0 end)
as SuccessCount,sum (case when ControResult=2 then 1 else 0 end) as failCount from
( select distinct ControResult,[MACID] ,CONVERT(varchar(100), ControlTime, 23) as ControlTime from [RemoteControlTryRecord]
as SuccessCount,sum (case when ControResult=2 then 1 else 0 end) as failCount from
( select distinct ControResult,[MACID] ,CONVERT(varchar(100), ControlTime, 23) as ControlTime from [RemoteControlTryRecord]
group by CONVERT(varchar(100), ControlTime, 23) ,ControResult,[MACID] ) as b group by [ControlTime]
其中先去除掉每台设备的重复数据,再将数据进行统计 。