个人网站: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://lanlanwork.gawce.com/quote/8631.html
阁恬下 http://lanlanwork.gawce.com/ , 查看更多