多对一关系的sql查询

I have a table with 3 columns

我有一个3列的表

table: StaffDepartmentAssgnment

表:StaffDepartmentAssgnment

StaffId         DepartmentId          AssignedFromDate

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

I want to find out all the StaffIds which are currently in Dept2.How do i write a query for it?

我想找出目前在Dept2的所有斯塔菲德家族成员。如何为它编写查询?

2 个解决方案

#1

This is a DB engine independent solution

这是一个DB引擎独立的解决方案。

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

#2

Perhaps:

可能:

SELECT DISTINCT sd.StaffId         
FROM StaffDepartmentAssgnment sd
WHERE sd.DepartmentId = 'Dept2'

Update

更新

S1 is not in Dept2 now,he has been shifted to Dept1..i need to pick up the top AssignedFromDate

S1现在不在部门2，他被调到部门1。我要去取最上面的订单

Since you're using SQL-Server you can use a CTE with the ROW_NUMBER function:

由于使用的是SQL-Server，所以可以使用带有ROW_NUMBER函数的CTE:

WITH CTE AS (
    SELECT sd.staffid, sd.DepartmentId,
           rn = Row_number() OVER ( 
                    Partition BY sd.staffid 
                    ORDER BY assignedfromdate DESC) 
    FROM   staffdepartmentassgnment sd) 
SELECT staffid 
FROM   cte 
WHERE  rn = 1 
AND    DepartmentId = 'Dept2'

Demo

演示