Mysql左连接分页查询

我会带着你远行 2021-09-24 13:18 382阅读 0赞

个人网站：[http://xiaocaoshare.com/][http_xiaocaoshare.com]

1.常见错误

select \* from user t1 left join user\_tages t2 on t2.userId=t1.id  limit 0,10

这种先连接查询在分页的是错误的，正常的是先分页，再连接查询

改进：（使用子查询）

<select id="queryCouponByToken" resultMap="BaseCouponResponseResultMap">
        SELECT t1.id,t1.program_token,t1.coupon_type,t1.name,t1.deduct,t1.discount,t1.valid_for_all_shop,
        t1.lower_consumer_amount,t2.value_id as t_id,t2.item_type as t_type,
        CASE
        when t2.item_type='CARD' THEN (SELECT card_name from card where id=t2.value_id and valid=1 and
        program_token=#{programToken})
        when t2.item_type='SERVICE' THEN (SELECT title from ord_service where id=t2.value_id and valid=1 and
        program_token=#{programToken})
        END
        as t_name,
        t1.valid_start_date,t1.valid_end_date,
        t1.amount,t1.remain_amount,t1.use_range,
        (SELECT count(user_info_id) from user_coupons where coupon_id=t1.id) as received_amount,
        (SELECT count(user_info_id) from user_coupons where coupon_id=t1.id and use_status=1) as used_amount,
        (SELECT COUNT(shop_id) from coupon_shop_relation where coupon_id=t1.id and valid=1) as able_shop_amount,
        CASE
        when t1.stop_grant_status=1 or NOW() > t1.valid_end_date THEN '已结束'
        when NOW() BETWEEN t1.valid_start_date and t1.valid_end_date THEN '进行中'
        else '未开始'
        END
        as status,
        CASE
        when t1.coupon_type='DISCOUNT' THEN '折扣券'
        when t1.coupon_type='DEDUCTION' THEN '抵扣券'
        ELSE '未知'
        END
        as coupon_type_name,
        CASE
        when t1.use_range='ALL' THEN '所有项目'
        when t1.use_range='PART' THEN '指定项目'
        ELSE '未知'
        END
        as use_range_name
        FROM
        (SELECT * from coupon t where t.program_token=#{programToken} and t.valid=1
        <if test="keyword != '' and keyword != null">
            AND t.name like #{keyword}
        </if>
        <if test="couponType != '' and couponType != null">
            AND t.coupon_type=#{couponType}
        </if>
        ORDER BY t.create_time desc limit
        #{start},#{pageSize} ) t1
        LEFT JOIN coupon_item_relation t2 on t1.id=t2.coupon_id
        ORDER BY t1.create_time DESC
    </select>

[http_xiaocaoshare.com]: http://xiaocaoshare.com/