Mybatis bind标签，sql标签，include标签

Why？

最近遇到一个场景，表(t_data)如下

id	A	B	C	D	len
1	NULL	90	93	92	342
2	89	89	NULL	86	452
3	98	78	89	78	236

A、B、C、D 字段为四种指标值，现在要查询每种指标的平均值(NULL值的需要过滤)，查询SQL:

select 
    a_total/a_total_len as a_average,
    b_total/b_total_len as b_average,
    c_total/c_total_len as c_average,
    d_total/d_total_len as d_average
from 
    (select 
        sum(ifnull(a,0)*len) as a_total,
        sum(if(a is null,0, len) ) as a_total_len，
        sum(ifnull(b,0)*len) as b_total,
        sum(if(b is null,0, len) ) as b_total_len，
        sum(ifnull(c,0)*len) as a_total,
        sum(if(c is null,0, len) ) as c_total_len，
        sum(ifnull(d,0)*len) as d_total,
        sum(if(d is null,0, len) ) as d_total_len
    from
        t_data
    ) t_temp

使用<bind>、<sql>、<include>实现优化SQL

<bind> 是什么？

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

<sql>、<include> 是什么？

这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。 参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

这个 SQL 片段可以在其它语句中使用，例如：

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

实际运用：

<!--中间表查找-->
<sql id="first_select_sql">
    select
    <foreach collection="colsList" item="item" open=" " separator="," close=" ">
        sum(ifnull(${item},0)*len) as ${item}_total,
        sum(if(${item} is null,0, len) ) as ${item}_total_len，
    </forech>
    from 
        t_data
</>

<!--平均值计算-->
<select id="select_average" resultType="AverageData">
    <bind name="colsList" value='{"A","B","C","D"}'>
    select 
    <foreach collection="colsList" item="item" open=" " separator="," close=" ">
        ${item}_total/ ${item}_total_len as  ${item}_average
    </forech>
    from
        (
            <include refid="first_select_sql"/>
        ) t_temp

</select>

总结：

• 使用<bind>标签定义一个数组变量{"A","B","C","D"}；
• 使用<sql>标签创建中间表查询sql片段
• 使用<include>引用sql片段