由浅入深编写SQL并使用MyBatis实现

1、SQL执行顺序

2、由浅入深编写SQL
表格：两个表格分别是【优惠券元数据表】和【优惠券领取表】
需求：查询某个用户在某个商户可以领取的优惠券列表（注：商户在发放优惠券时对单个用户可领数量有限制）
（1）表连接【用到join…on】
查询用户在某个商户已经领取的优惠券列表

SELECT  c.coupon_batch_id,c.max_num_owner from coupon_cooperative c LEFT JOIN coupon_favorite_cooperative t on c.coupon_batch_id=t.coupon_batch_id where t.chnl_id='1BFLTU0HN90FHT001GKO4G0MDMKFUTRB' and c.shop_entity_id='1BG5EVHAHP01VM001GKQK9A13R8KREO1'结果如下：max_num_owner为商户定义每个用户最多能领取多少张

（2）查询当前用户已经领取数量与商户限制数量【用到group by和count】
首先需要按优惠券ID进行分组，然后分别计算已经领取数量

SELECT c.coupon_batch_id,c.max_num_owner ,count(c.coupon_batch_id) from coupon_cooperative c LEFT JOIN coupon_favorite_cooperative t on c.coupon_batch_id=t.coupon_batch_id where t.chnl_id='1BFLTU0HN90FHT001GKO4G0MDMKFUTRB' and c.shop_entity_id='1BG5EVHAHP01VM001GKQK9A13R8KREO1'  GROUP BY c.coupon_batch_id

结果如下：

（3）筛选已经领取数量小于商户限制数量的优惠券ID【用到having】
比较是在分组计算好的前提下，由于where在group by前面执行，因此比较不能放在where后面，
只能使用having

SELECT c.coupon_batch_id,c.max_num_owner,count(c.coupon_batch_id) from coupon_cooperative c LEFT JOIN coupon_favorite_cooperative t on c.coupon_batch_id=t.coupon_batch_id where t.chnl_id='1BFLTU0HN90FHT001GKO4G0MDMKFUTRB'  and c.shop_entity_id='1BG5EVHAHP01VM001GKQK9A13R8KREO1'  GROUP BY c.coupon_batch_id HAVING count(c.coupon_batch_id) < max_num_owner 

（4）对筛选出的记录进行排序【用到order by】

SELECT  c.coupon_batch_id,c.max_num_owner from coupon_cooperative c LEFT JOIN coupon_favorite_cooperative t on c.coupon_batch_id=t.coupon_batch_id where t.chnl_id='1BFLTU0HN90FHT001GKO4G0MDMKFUTRB' and c.shop_entity_id='1BG5EVHAHP01VM001GKQK9A13R8KREO1'  GROUP BY c.coupon_batch_id HAVING count(c.coupon_batch_id) < max_num_owner  ORDER BY c.org_id

（5）得到目标结果集后，分页返回，【用到limit】

SELECT  c.coupon_batch_id,c.max_num_owner from coupon_cooperative c LEFT JOIN coupon_favorite_cooperative t on c.coupon_batch_id=t.coupon_batch_id where t.chnl_id='1BFLTU0HN90FHT001GKO4G0MDMKFUTRB' and c.shop_entity_id='1BG5EVHAHP01VM001GKQK9A13R8KREO1'  GROUP BY c.coupon_batch_id HAVING count(c.coupon_batch_id) < max_num_owner  ORDER BY c.org_id limit 2,2

3、使用Mybatis Mapper实现上述SQL

 <select id="selectByMemberAndShopEntityId" parameterType="xxxxx.vo.manual.UserShopEntityCouponExample" resultMap="BaseResultMapJustCoupon">            select        <if test="distinct">          distinct        </if>        <include refid="Base_Column_List" />        from coupon_cooperative coupon LEFT JOIN coupon_favorite_cooperative fav on coupon.coupon_batch_id=fav.coupon_batch_id        <where>            <if test="couponStatus != null">              coupon.status=#{couponStatus,jdbcType=INTEGER}            </if>            <choose>                <when test="isShopSpecified">                     and coupon.shop_entity_id=#{shopEntityId,jdbcType=VARCHAR}                </when>                <otherwise>                    and coupon.shop_entity_id &lt;&gt; #{shopEntityId,jdbcType=VARCHAR}                 </otherwise>            </choose>            <if test="chnlId != null">                and ( fav.chnl_id=#{chnlId,jdbcType=VARCHAR} or ISNULL(fav.chnl_id) )             </if>                GROUP BY coupon.coupon_batch_id HAVING count(coupon.coupon_batch_id) &lt; coupon.max_num_owner             <if test="orderByClause != null">                order by ${orderByClause}            </if>            <if test="limit != null">              <if test="offset != null">                limit ${offset}, ${limit}              </if>              <if test="offset == null">                limit ${limit}              </if>            </if>        </where>  </select>

public class UserShopEntityCouponExample{    private String chnlId;    private String shopEntityId;    private Integer couponStatus;    private Integer limit;    private Integer offset;    private boolean distinct;    private String orderByClause; }

4、注意事项

（1）xml中字符转义：< 使用$lt;表示，双引号使用&quot;表示，单引号使用 &apos;表示（2）mapper中使用#和$的区别JDBC Sql：在JDBC中有两种Sql编写方式，一种是支持参数化和预编译的PrepareStatement，一种是支持原生Sql的Statement，有Sql注入的风险mapper中#和$分别类似于PrepareStatement和Statement1. #默认将传入数据都当成一个字符串，自动给传入数据加一个双引号。如：order by #{org_id}，如果传入的值是1,那么解析成sql时的值为order by "1"，如果不想被当做字符串，可以显式指定传入数据类型，coupon.status=#{couponStatus,jdbcType=INTEGER}2.  $默认将传入数据直接合并到sql中。如：order by ${org_id}，如果传入的值是1,那么解析成sql时的值为order by 13.  #能最大程序防范sql注入，$不行，因此尽可能使用#