多对一关系的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的StaffIds，如何编写查询语句？

- Prachi Pant

---

S1现在不在Dept2了，他已经被调到了Dept1。我需要选择最高的AssignedFromDate。 - Prachi Pant

请使用编辑按钮将您的评论添加到问题中。 - Jodrell

@Jodrell 是的，员工可以重新分配..相应的日期将插入到AssignedFromDate中..有解决方案吗？ - Prachi Pant

@PrachiPant：我不知道你是否注意到，根据你在评论中提供的新信息，我已经更新了我的答案。 - Tim Schmelter

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

- juergen d

---

S1现在不在Dept2了，他已经被调到了Dept1。我需要选择最顶部的AssignedFromDate。 - Prachi Pant

@PrachiPant，请在问题中详细说明您的评论。 - Jodrell

@PrachiPant：你可以为日期添加一个条件。你使用的是哪个数据库引擎？ - juergen d

我正在使用Microsoft SQL Server。我猜没有任何日期会大于current_timestamp。 - Prachi Pant

Answer 2

也许：

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

更新

S1现在不在Dept2中，他已经转移到了Dept1..我需要挑选出最高的AssignedFromDate

由于您正在使用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

- Tim Schmelter