- 一对多查询(不用分页的情况)
使用mybatis的 collection
<collection property="多的属性名" ofType="多的实体类型" >
<result column="多的数据库字段" property="映射到实体类型"></result>
</collection>
eg 一个景区对应多张门票
<collection property="ticketVos" ofType="com.tengyun.dimall.common.vo.ticket.TicketVo">
<result column="bId" property="id"></result>
<result column="ticket_name" property="ticketName"></result>
</collection>
一对多查询不分页的情况下之间左连接多表即可
2. 一对多查询分页(第一种,细品)
SELECT
aa.ID,
aa.scenic_name,
aa.longitude,
aa.latitude,
aa.address,
aa.cover,
C.ticket_name,
c.cancel_rule,
c.available_type,
c.code as code,
e.NAME AS labelName,
d.special_price,
d.day_time
FROM
(
SELECT
a.*
FROM scenic a
<include refid="getTicketApiListByKeyWordsCondition"></include>
<bind name="start" value="ticketApiListQueryVo.fetchStart()"/>
<bind name="limit" value="ticketApiListQueryVo.fetchLimit()"/>
limit #{limit} offset #{start}
)aa
INNER JOIN ticket c ON c.scenic_id = aa.id and c.check_status = 1 and c.is_deleted = 0 and c.on_sale =1
LEFT JOIN ticket_price d ON c.ID = d.ticket_id and d.special_stock > 0
left JOIN scenic_label b ON b.scenic_id = aa.id
LEFT JOIN label e ON e.id = b.label_id
<sql id="getTicketApiListByKeyWordsCondition">
where a.check_status = 1 and a.is_deleted = 0
<if test="ticketApiListQueryVo.labelId != null">
and exists (select 1 from scenic_label r where r.label_id = #{ticketApiListQueryVo.labelId} and r.ticket_id = a.id)
</if>
<if test="ticketApiListQueryVo.keyWords != null and !''.toString().equals(ticketApiListQueryVo.keyWords)">
and (a.scenic_name LIKE concat ( '%', #{ticketApiListQueryVo.keyWords}, '%' )
or exists
( select 1 from ticket t
LEFT JOIN ticket_price d ON t.ID = d.ticket_id
where t.scenic_id = a.id and t.check_status = 1 and t.on_sale = 1
and t.is_deleted = 0 and d.special_stock > 0
<if test="ticketApiListQueryVo.dayTime != null">
and d.day_time = #{ticketApiListQueryVo.dayTime}
</if>
and t.ticket_name like concat ('%', #{ticketApiListQueryVo.keyWords}, '%')
)
)
</if>