sql-server-2008 - 返回给定日期的正确数据。

Question

全部，

我有两个表，一个叫 Employee，另一个叫 Target Ratio。他们通过 FK EmployeeID 相关

tblEmployee
EmployeeID FirstName LastName
1              John     Doe

tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
      1            1        1/1/2012     8
      2            1        6/1/2012     5
      3            1        9/1/2012     7

我的问题是如何查询 tblTargetRatio 表以返回以下情况的正确记录:

1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)

谢谢，艾伦

Answer 1

看来您正在寻找最近的生效日期。您可以通过查找小于被搜索值的最大值来做到这一点(其中 @Date 是被搜索的日期):

select top 1 * from tblTargetRatio
where employeeid = 1 and effectivedate < @Date
order by effectivedate desc

演示:http://www.sqlfiddle.com/#!3/f6d4b/2