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
