mybatis关联查询，同表多次leftjoin
商品表中有三级分类：classify1Id、classify2Id、classify3Id，关联分类表中id，查询商品时需要关联查询出这三级分类所对应的分类数据，所以需要多次left join。
商品表：
分类表：
因为是同⼀张表多次join，所以需要给分类表起个别名。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="all.dao.GoodsMapper">
<resultMap id="goodsMap" type="all.vo.GoodsVo">
<id property="id" column="id"></id>
<result property="goodsName" column="goods_name"></result>
<result property="goodsPrice" column="goods_price"></result>
<result property="commentCount" column="comment_count"></result>
<result property="sales" column="sales"></result>
<result property="brandId" column="brand_id"></result>
<result property="classify1Id" column="classify1_id"></result>
<result property="classify2Id" column="classify2_id"></result>
<result property="classify3Id" column="classify3_id"></result>
<result property="createTime" column="create_time"></result>
<result property="status" column="status"></result>
<result property="step" column="step"></result>
<result property="cover" column="cover"></result>
<result property="details" column="details"></result>
<!--⼀级分类对应数据-->
<association property="classify1" javaType="ity.ClassifyDto" columnPrefix="classify1_"> <id property="id" column="id"></id>
<result property="name" column="name"/>
</association>
<!--⼆级分类对应数据-->
<association property="classify2" javaType="ity.ClassifyDto" columnPrefix="classify2_"> <id property="id" column="id"></id>
<result property="name" column="name"/>
</association>多表left join
<!--三级分类对应数据-->
<association property="classify3" javaType="ity.ClassifyDto" columnPrefix="classify3_"> <id property="id" column="id"></id>
<result property="name" column="name"/>
</association>
</resultMap>
<select id="goodsList" parameterType="ity.GoodsDto" resultMap="goodsMap">
select goods.*,
classify1.id as classify1_id,
classify1.name as classify1_name,
classify2.id as classify2_id,
classify2.name as classify2_name,
classify3.id as classify3_id,
classify3.name as classify3_name
from goods
left join classify as classify1 on goods.classify1_id=classify1.id
left join classify as classify2 on goods.classify2_id=classify2.id
left join classify as classify3 on goods.classify3_id=classify3.id
where goods.del=0
</select>
</mapper>
返回结果：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。