我有一个历史表,我想获得一个员工的最新修改。我有这个例子,最大值总是得到一条记录?
CREATE TABLE EmployeeHIST
(
Id INT PRIMARY KEY,
EmployeeId INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
ModifiedDate DATETIME
)
INSERT INTO EmployeeHIST VALUES (1, 1, 'Jhon', 'Doo', '2013-01-24 23:45:12')
INSERT INTO EmployeeHIST VALUES (2, 1, 'Jhon', 'Doo', '2013-02-24 15:45:12')
INSERT INTO EmployeeHIST VALUES (3, 1, 'Jhon', 'Doo', '2013-02-24 15:45:12')
SELECT EmployeeId, MAX([ModifiedDate])
FROM EmployeeHIST
WHERE EmployeeId = 1
GROUP BY EmployeeId
好的,是的,你是对的,但是如果我需要获得EmployeeId = 1的Id列,在这种情况下,我将收到两个值2和3,所以我需要应用顶部的一个对吗?
Max()为Group By中定义的每个值的组合带来一条记录。
在你的样本数据中,总是有一条记录。
如果您是Group By Id,EmployeeId,您将获得三条记录,因为这些值有三种唯一的组合。
这也适用于其他聚合函数,如Min()、Avg()、Count()等如果您想获得具有max(date)的记录的id,那么您有以下选项(可能有更好的选项):
;With MyCTE AS
(
SELECT EmployeeId, MAX([ModifiedDate]) AS MaxDate
FROM EmployeeHIST
GROUP BY EmployeeId
)
SELECT E.Id,E.EmployeeId,ModifiedDate
FROM EmployeeHIST E
JOIN MyCTE M
ON M.EmployeeId = E.EmployeeId
AND M.MaxDate = E.ModifiedDate
WHERE E.EmployeeId = 1
SQLFiddle 1
现在,在本例中,返回了id 2和3。我不知道这里的业务需求是什么,但我相信您只希望返回3个,所以下面是一个解决方案:
;With MyCTE AS
(
SELECT EmployeeId, MAX([ModifiedDate]) AS MaxDate
FROM EmployeeHIST
GROUP BY EmployeeId
)
SELECT MAX(E.Id),E.EmployeeId,ModifiedDate
FROM EmployeeHIST E
JOIN MyCTE M
ON M.EmployeeId = E.EmployeeId
AND M.MaxDate = E.ModifiedDate
WHERE E.EmployeeId = 1
GROUP BY E.EmployeeId,ModifiedDate
SQLFiddle 2