Mysql左连接分页查询

个人网站：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>