2段SQL2005的SQL的比较-优快云博客

本文对比了两种SQL查询方法A和B，方法A通过筛选签出为空再获取签入时间的最大值；方法B则先找出最新的签入时间再判断签出状态。考虑到客户可能存在的非正常退出情况，推荐使用方法B。

检索出现在未签出的分机

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

与

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.以兼容客户的非正常退出的情况.