SQL select max(date)和相应的值

发布时间：2020-12-12 16:14:54 所属栏目：MsSql教程来源：网络整理

导读：Possible Duplicate: 07000 我有一个聚合查询,如下所示： SELECT TrainingID,Max(CompletedDate) as CompletedDate,Max(Notes) as Notes --This will only return the longest notes entryFROM HR_EmployeeTrainings ETWHERE (ET.AvantiRecID IS NULL OR ET.

Possible Duplicate:
07000

我有一个聚合查询,如下所示：

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会做什么？我甚至不确定我想要得到的是最大(completedDate)记录中的注释字段.我该怎么做这样做？

解决方法

您可以使用子查询.子查询将获得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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!