由浅入深编写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和Statement

1. #默认将传入数据都当成一个字符串，自动给传入数据加一个双引号。如：order by #{org_id}，如果传入的值是1,那么解析成sql时的值为order by "1"，如果不想被当做字符串，可以显式指定传入数据类型，coupon.status=#{couponStatus,jdbcType=INTEGER}
2.  $默认将传入数据直接合并到sql中。如：order by ${org_id}，如果传入的值是1,那么解析成sql时的值为order by 1
3.  #能最大程序防范sql注入，$不行，因此尽可能使用#