多对一关系的sql查询

Question

我有一个包含 3 列的表格

表:StaffDepartmentAssgnment

StaffId         DepartmentId          AssignedFromDate

S1              Dept1                 2013-02-08
S2              Dept1                 2013-02-08
S3              Dept2                 2013-02-01 
S1              Dept2                 2013-02-01

我想找出当前在 Dept2 中的所有 StaffId。我该如何为它编写查询？

Answer 1

这是一个独立于数据库引擎的解决方案

select * from 
( 
   select StaffId, max(AssignedFromDate) as adate
   from StaffDepartmentAssgnment 
   group by staffid
) x
inner join StaffDepartmentAssgnment y
      on y.staffid = x.staffid and adate = y.AssignedFromDate
where DepartmentId = 'dept2'

SQLFiddle demo