前言:Sql Server根据唯一索引和数值字段,将多条过滤为单条
需求:根据ResumeUuid(唯一索引)和Degree(数值),将ResumeUuid相同的数据中,过滤出Degree值最大的数据。
代码:(第一条SQL为过滤后的效果,第二条为原数据)
select b.EducationId,EducationUuid,School,Major,degree from (
select ResumeUuid, max(Degree) as max_age from Education
where 1 = 1 group by ResumeUuid
) a
left join Education b on (b.ResumeUuid = a.ResumeUuid and b.Degree = a.max_age)
where a.ResumeUuid = 'e5aed511-5083-4ffb-948e-d67c3c07747a'
select EducationId,EducationUuid,School,Major,degree from Education
where ResumeUuid = 'e5aed511-5083-4ffb-948e-d67c3c07747a'
分析(图一):
1:通过唯一索引(ResumeUuid)分组,并将数值字段(degree)的最大值求出,然后作为主表;
2:再通过自连接,条件为唯一索引和数值字段,关联起来;
3:将原信息带出,即可达到这样的过滤
图一
文章参考: