查询一个表中某个字段最大值的集合

时间:2021-10-09 12:44:37
 

1、使用场景

  需要找到最新版本的Resume信息,根据主键

2、所有信息在同一个表中查询出,创建子查询 

<!-- 查询一组最新版本的简历信息 -->
<select id="listByResumeIds" resultMap="BaseResultMap" parameterType="java.util.List">
SELECT
<include refid="Base_Column_List" />
FROM
T_RESUME_INFO_VERSION resume
WHERE
resume.version in (
SELECT vlist.versionList from (
SELECT rv.resume_id AS resume_id,MAX(VERSION) as versionList
FROM T_RESUME_INFO_VERSION rv
WHERE rv.RESUME_ID in
<foreach collection="resumeIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
group by
rv.resume_id DESC) as vlist)
AND
resume.RESUME_ID in
<foreach collection="resumeIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>