一对多查询(分页)

  1. 一对多查询(不用分页的情况)

        使用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>

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值