SQL Server:查找公司/部门 ID 的最新生效日期

Question

我们有一个DeptID表，每个公司/部门ID/操作都有多条记录。如何提取仅包含公司/部门 ID 的最新生效日期的列表，而不考虑操作？我想在查询的记录中显示这些列和其他一些列。

Co#   DeptID  Action  Eff Date
---   ------  ------  --------
01    12      Closed  03/22/2018 
01    24      Closed  03/03/2014 
01    24      Open    07/01/2010
01    365     Closed  01/01/2008
01    365     Open    05/01/2010

这个SQL很接近，但我无法让它只列出该操作的最后一个生效日期。如果我删除该操作，那么它将列出公司/deptID 的最后生效日期，但我需要知道它适用于哪个操作 - 打开或关闭？

select distinct 
loc.CompanyNumber, loc.DeptID, loc.Action, 
max(loc.effdate) over (partition by companynumber, deptid) as LocmaxDate
from 
[dbo].[LocationsOpenClosed] as loc

Answer 1

您可以使用subquery：

select loc.*
from [dbo].[LocationsOpenClosed] as loc
where effdate = (select max(loc1.effdate) 
from [dbo].[LocationsOpenClosed] as loc1
where loc1.companynumber = loc.companynumber 
loc1.deptid = loc.deptid
);

但是，我也建议使用排名函数：

select *
from (select *, 
row_number() over (partition by companynumber, deptid order by effdate desc) as seq
from [dbo].[LocationsOpenClosed]
) loc
where seq = 1;

Answer 2

您可以将 CTE(Common Table Expression(与 Rownumber(( 结合使用，如下所示：

; with 
cte as 
(select loc.CompanyNumber
, loc.DeptId
, loc.Action
, loc. EffDate
, RowNumber() over (partition by loc.CompanyNumber, loc.DeptId order by loc.EffDate desc) as RN
from    [dbo].[LocationsOpenClosed] as loc
) 
select  cte.CompanyNumber
, cte.DeptId
, cte.Action
, cte.EffDate
from    cte
where   1 = 1
and cte.RN = 1