ibatis联合查询

ibatis联合查询，ibatis执行是顺序执行，所以要把被引用的sql语句放在调用之前

<sql id="selectSqlUser">
    <![CDATA[
        select
            a.su_id,
            a.su_code,
            a.su_name,
            a.su_contact,
            a.su_type,
            a.so_id,
            a.wb_type,
            (select b.su_name from sys_user b where b.su_id=a.so_id) as so_name,
            a.su_order,
            (case when exists(select 1 from sys_user b where b.so_id = a.su_id)  then '1' else '0' end) as so_child,
        sum(b.fs_transaction_amount) as fs_transaction_amount,
        sum(b.fs_user_id) as fs_user_id

            from sys_user a left join fsms_fee_splitting b on a.su_name=b.fs_parent_id
    ]]>
</sql>
<sql id="whereTemplateUser">
    <dynamic prepend="where">

        <isNotEmpty prepend="and" property="so_id">
            <![CDATA[ a.so_id like #so_id:VARCHAR#+'%']]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_code">
            <![CDATA[ a.su_code like '%'+#su_code:VARCHAR#+'%' ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_name">
            <![CDATA[ a.su_name like '%'+#su_name:VARCHAR#+'%']]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_job">
            <![CDATA[ a.su_job like '%'+#su_job:VARCHAR#+'%']]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_sex">
            <![CDATA[ a.su_sex=#su_sex:VARCHAR# ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_status">
            <![CDATA[ a.su_status=#su_status:VARCHAR# ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_contact">
            <![CDATA[ a.su_contact like '%'+#su_contact:VARCHAR#+'%' ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_email">
            <![CDATA[ a.su_email like '%'+#su_email:VARCHAR#+'%' ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="su_type">
            <![CDATA[ a.su_type in ($su_type$) ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="wb_type">
            <![CDATA[ a.wb_type=#wb_type:NUMERIC# ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="fs_cretae_time_begin"><!--创建时间开始-->
            <![CDATA[ b.fs_cretae_time between CONVERT(datetime,#fs_cretae_time_begin#+' 00:00:00') and CONVERT(datetime,#fs_end_time_end#+' 23:59:59')]]>
        </isNotEmpty>
        <isNotEmpty prepend="or" property="fs_end_time_end"><!--截止时间结束-->
            <![CDATA[ b.fs_end_time between CONVERT(datetime,#fs_cretae_time_begin#+' 00:00:00') and CONVERT(datetime,#fs_end_time_end#+' 23:59:59') ]]>
        </isNotEmpty>
    </dynamic>
</sql>
<!--User翻页列表-->
<select id="pageListUser" parameterClass="PageParam" resultClass="sys_user">
    <![CDATA[ $head$ ]]>
    <include refid="selectSqlUser"/>
    <include refid="whereTemplateUser"/>
    group by a.su_id,
             a.su_code,    a.su_name,         a.su_contact,             a.su_type,             a.so_id,             a.wb_type,             a.su_order    <![CDATA[ $sortOrder$ ]]>    <![CDATA[ $foot$ ]]></select><!--User翻页记录数,联合查询--><select id="pageCountUser" parameterClass="PageParam" resultClass="Long">    <![CDATA[ select count(*) total from (             select count(*) total  from sys_user a left join fsms_fee_splitting b on a.su_name=b.fs_parent_id    ]]>    <include refid="whereTemplateUser"/>    <![CDATA[              group by    a.su_id) as temp    ]]></select>