group by 用法解决方法

group by 用法

表My_allScore字段OrderId,AttemptId , RegID , EmpId ,StartDt ,EndDt ,Success ,Score ,EmployeeNumber ,EmpFName ,EmpLName ,PrimaryDomain

–1.求每个员工的最高成绩和AttemptId

不查AttemptId结果是对的，但是最高成绩有重复，所以要查AttemptId，但是结果又不对了。

SELECT max(Score),MS.AttemptId,EmpId FROM #My_allScore group by MS.EmpId ,MS.AttemptId order by EmpId

–2.求每个员工的最近一次和AttemptId

SQL code

1 select A.empid,A.score,A.attemptid
  from My_allScore A
  where not exists (select 1 from My_allScore B where A.EmpId =B.EmpId and A.Score<B.Score )

2 select A.empid,A.attemptid
  from My_allScore A
  where not exists (select 1 from My_allScore B where A.EmpId =B.EmpId and A.OrderId<B.OrderId)

相关文章：

你感兴趣的文章：

标签云：