如何仅返回基于日期的最近记录
问题描述:
我需要让我的查询返回仅基于员工开始日期的最新记录。我想我需要一个最大日期内连接,但我无法让它工作。有人对我有任何建议吗?我正在使用TOAD for SQL Developer。如何仅返回基于日期的最近记录
SELECT
e.No_,
ep.[Classification Description],
ep.[Employee Starting Date],
ep.[Standard Rate],
ep.[Over Award Rate],
ep.[Employee Rate],
p.[Job Title],
e.[First Name],
e.[Last Name],
e.[Payroll No_]
FROM Employee e,
[Payroll Employee] p,
[Employee Pay Rate] ep
WHERE e.No_ = p.[Employee No_]
AND e.No_ = ep.[Employee No_]
AND ep.[Employee No_] = p.[Employee No_]
AND e.[Payroll No_] = 'PAY7'
AND CAST(e.[Termination Date] AS date) = '1753-01-01'
答
您可以通过做群组,并获得最小或最大日期如下:
SELECT
e.No_,
ep.[Classification Description],
MAX(ep.[Employee Starting Date]) as [Employee Starting Date], --hope it is min based on your logic
ep.[Standard Rate],
ep.[Over Award Rate],
ep.[Employee Rate],
p.[Job Title],
e.[First Name],
e.[Last Name],
e.[Payroll No_]
FROM Employee e
INNER JOIN [Payroll Employee] p
ON e.No_ = p.[Employee No_]
INNER JOIN [Employee Pay Rate] ep
ON e.No_ = ep.[Employee No_]
AND ep.[Employee No_] = p.[Employee No_]
WHERE
e.[Payroll No_] = 'PAY7'
AND CAST(e.[Termination Date] AS date) = '1753-01-01'
GROUP BY
e.No_,
ep.[Classification Description],
ep.[Standard Rate],
ep.[Over Award Rate],
ep.[Employee Rate],
p.[Job Title],
e.[First Name],
e.[Last Name],
e.[Payroll No_]
你希望每一个员工最近的记录或者你只是想最近的记录?哪一天? '员工开始日期'? –
添加模式示例数据和所需的输出 –
附加问题..这是真的吗? ''1753-01-01'' –