仅在满足特定条件时选择最后记录

Question

我有一个包含ID，WorkerID，IsActive标志（1或0）和LocalTime的表。每次工作人员处于活动状态或不活动状态时，都会使用WorkerID，1或0标志记录以及一个时间（LocalTime）创建一条记录。

我想插入一个新表中：从该表中，对于每个唯一的WorkerID，仅当该记录的IsActive标志为1时，才为该唯一的WorkerID选择具有最新LocalTime的记录的WorkerID，LocalTime和LocalTime
+
Time。对于WorkerID，具有最新LocalTime的记录的IsActive值为1，请选择它。否则，如果对于WorkerID，具有最新LocalTime的记录的IsActive值为0，请不要选择它。

例子：

ID  WorkerID    IsActive    LocalTime
1   11      1       21/04/2015
2   22      0       22/04/2015
3   11      0       21/04/2015
4   22      1       22/04/2015

仅应选择ID为4的记录。

Answer 1

--get unique worker ids
select distinct WorkerId
from MyTable a
--which are active
where IsActive=1
--that don't have any worker ids on a more recent time
--(ignoring status as if there's a more recent active record for 
--this worker we want that record, not this one; if there's a more 
--recent inactive record we don't want this one anyway)
and not exists
(
    select top 1 1
    from MyTable b
    where b.WorkerId = a.WorkerId
    and b.LocalTime > a.LocalTime
)