需求
最近在项目中遇到一个问题，需要检验一个三级联动关系是否正确。当用户通过Excel模板导入数据时，后台需要校验序列、岗位、级别这个三级联动的关系是否匹配，不匹配的话需要给出提示。
项目用到的数据库是mysql+tk.mybatis,最初的想法是能否通过一个sql语句来实现查询所有的这三级联动对应关系，比如序列有[a,b],岗位有[c,d,e],其与序列对应的关系是[{a:c},{a:d},{b:e}]，级别有[1,2],与岗位对应的关系是[{c:1},{c:2},{d:1},{e:1},{e:2}],我想通过一条sql查询得到结果如下:
实现
折腾了一番最终得以实现:
mapper文件中sql语句：
```
<select id="getSequenceRankRankLevelList" resultMap="sequenceRankRankLevelMap">
SELECT DISTINCT(sequence),rank,rank_level FROM xxx
where sequence is not null and rank is not null and rank_level is not null
</select>
<resultMap id="sequenceRankRankLevelMap" type="com.baidu.hcm.ehr.pojo.vo.budget.SequenceRankRankLevelVO">
<result column="sequence" jdbcType="VARCHAR" property="sequence" />
<collection property="rankAndRankLevelVOS" ofType="com.baidu.hcm.ehr.pojo.vo.budget.RankAndRankLevelVO">
<result column="rank" jdbcType="VARCHAR" property="rank" />
<collection property="rankLevels" ofType="com.baidu.hcm.ehr.pojo.vo.budget.RankLevelAndPriceVO">
<result column="rank_level" jdbcType="VARCHAR" property="rankLevel" />
</resultMap>
```
对应的mapper类：
```
public interface XXXMapperextends BaseMapper {
List<SequenceRankRankLevelVO> getSequenceRankRankLevelList();
}
```
对应的vo类：
```
public class SequenceRankRankLevelVO {
private Stringsequence;
private List<RankAndRankLevelVO> rankAndRankLevelVOS;
}
public class RankAndRankLevelVO {
private Stringrank;
private List<RankLevelAndPriceVO> rankLevels;
}
public class RankLevelAndPriceVO {
private StringrankLevel;
}
```
service类中的方法如下：
```
@Autowired
private XXXMapper xxxMapper;
public ListsequenceRankRankLevel() {
List<SequenceRankRankLevelVO> sequenceRankRankLevelList =xxxMapper.getSequenceRankRankLevelList();
}
```
最终在service中调用该方法后会得到三级联动的正确关系
