检索出现在未签出的分机
A:
select TblAgentSummary.StationDeviceID,TblAgentSummary.LoginDateTime,SubString(TblAgentSummary.AgentID,1,4) as AgentID from
(select StationDeviceID,
Max(LoginDateTime) as LoginDateTime
from TblAgentSummary where LogoutDateTime is null
and LoginDateTime >= CAST(getDate()-7 AS char(12))
group by StationDeviceid-- having LoginDateTime = Max(LoginDateTime)
) a inner join TblAgentSummary on a.StationDeviceid = TblAgentSummary.StationDeviceid
and a.LoginDateTime = TblAgentSummary.LoginDateTime
order by TblAgentSummary.StationDeviceID desc
与
B:
select s.* from
tblAgentSummary s
inner join (
select max(LoginDateTime) LastTime,StationDeviceID
from tblAgentSummary
group by StationDeviceID ) a on a.LastTime = s.LoginDateTime and a.StationDeviceID = s.StationDeviceID
where logoutdatetime is null
的业务中 :
A 是先检索签出时间为NULL再MAX签入时间.
B 是先检索最后签入时间,再查询是否为NULL.
应该选择B.以兼容客户的非正常退出的情况.