这是场景。我有员工,他们每年对不同的类别进行评估。我需要:每个类别的最新评估的整行。
例子:
员工表:
EmpID Name
454 John
评估表:
id EmpID Category EvalDate Note
1 454 A 2016-01-01 Excellent
2 454 A 2017-02-15 Not so good
3 454 B 2016-01-01 Poor
4 454 B 2017-02-01 Good
我需要一个查询,返回每个类别的最新评估(整行):
EmpID Category EvalDate Note
454 A 2017-02-15 Not so good
454 B 2017-02-01 Good
到目前为止我的问题是:
SELECT
evals.EmpID,
evals.Category,
evals.Note,
MAX(evals.EvalDate) as LatestEval
FROM
evals
WHERE
evals.EmpID = 454
GROUP BY
evals.Category
很遗憾,这将返回每个类别的最新日期,但返回旧evals的note字段。
最佳答案
你可以试试:
SELECT
evals.EmpID,
evals.Category,
evals.Note,
evals.EvalDate
FROM Employee emp
JOIN Evals evals
ON emp.EmpID = evals.EmpID
AND eval.EvalDate =
(SELECT MAX(EvalDate)
FROM Evals a
WHERE a.Category = evals.category
AND a.EmpID = emp.EmpID GROUP BY e.EmpID,e.Category)
GROUP BY evals.EmpID,evals.Category