Possible Duplicate:
How to get the record of a table who contains the maximum value?




我有一个汇总查询，如下所示：

SELECT TrainingID, Max(CompletedDate) as CompletedDate, Max(Notes) as Notes     --This will only return the longest notes entry
FROM HR_EmployeeTrainings ET
WHERE (ET.AvantiRecID IS NULL OR ET.AvantiRecID = @avantiRecID)
GROUP BY AvantiRecID, TrainingID

哪个可以正常工作，并且大多数时候返回正确的数据，但是我注意到了一个问题。返回的Notes字段不一定与max（completedDate）来自的记录匹配。相反，它将是具有最长字符串的那个？还是ASCII值最高的那个？如果两个记录之间有关联，SQL Server会做什么？我什至不确定。我要获取的是max（completedDate）记录中的notes字段。我应该怎么做呢？

您可以使用子查询。子查询将得到Max(CompletedDate)。然后，您使用此值并再次加入表中以检索与该日期关联的注释：

select ET1.TrainingID,
  ET1.CompletedDate,
  ET1.Notes
from HR_EmployeeTrainings ET1
inner join
(
  select Max(CompletedDate) CompletedDate, TrainingID
  from HR_EmployeeTrainings
  --where AvantiRecID IS NULL OR AvantiRecID = @avantiRecID
  group by TrainingID
) ET2
  on ET1.TrainingID = ET2.TrainingID
  and ET1.CompletedDate = ET2.CompletedDate
where ET1.AvantiRecID IS NULL OR ET1.AvantiRecID = @avantiRecID