查询以包括用于只显示一个输出的第一条记录和最后一条记录

Question

如何检索Validfrom是MIN，Validto是MAX，只显示最后一条最大记录？

select *
from Employee
where the Staff ID = A123456

当前结果

Staff ID, Staff Full Name, Staff English Name, Position, Division, Department, Section, VALIDFROM, VALIDTO    
A123456,Chan Tai Man,Peter Chan,CLERK,MARKETING,SUPPORT SERVICES,NONE,**2000-01-01 00:00:00.000**,2014-12-31 23:59:59.000    
A123456,Chan Tai Man,Peter Chan,SUPERVISOR,MARKETING,SUPPORT SERVICES,NONE,2005-01-01 00:00:00.000,2010-12-31 23:59:59.000    
**A123456,Chan Tai Man,Peter Chan,MANAGER,MARKETING,SUPPORT SERVICES,NONE**,2011-01-01 00:00:00.000,**2016-12-31 23:59:59.000**

以上BOLD的预期结果

Staff ID,Staff Full Name,Staff English Name,Position,Division,Department,Section,VALIDFROM,VALIDTO    
A123456,Chan Tai Man,Peter Chan,MANAGER,MARKETING,SUPPORT SERVICES,NONE,2000-01-01 00:00:00.000,2016-12-31 23:59:59.000

Answer 1

似乎您想要VALIDFROM的MIN()，但其余列来自VALIDTO是最新的行

FIRST_VALUE(VALIDFROM)为您提供VALIDFROM的第一个值/分钟

ROW_NUMBER()...为您提供具有最新VALIDTO的行

SELECT *
FROM   (
SELECT *,
MIN_VALIDFROM = FIRST_VALUE(VALIDFROM) OVER (PARTITION BY [STAFF ID]
ORDER BY VALIDFROM),
RN = ROW_NUMBER() OVER (PARTITION BY [STAFF ID] 
ORDER BY VALIDTO DESC)
FROM   Employee
) e
WHERE  e.RN = 1
AND    e.[Staff ID] = 'A123456'