sql的一种配置新方法

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pingan.ela.behavior.mapper.ElaTraceMapper">

    <sql id="select_column">
        e.id_ela id,
        e.order_no  orderNo,
        e.agent_no  agentNo,
        e.product_name productName,
        e.product_code productCode,
        c.ins_name insName,
        substring(c.ins_name FROM char_length(c.ins_name) FOR char_length(c.ins_name)) insLastChar,
        c.ins_sex  insSex,
        c.ins_age  insAge,
        c.ins_birthday insBirthday,
        c.ins_mobile insMobile,
        e.document_status documentStatus,
        e.response_time responseTime,
        e.source source,
        c.openid openid,
        e.money money,
        e.created_by        createdBy,
        e.created_date      createdDate,
        e.updated_by        updatedBy,
        e.updated_date      updatedDate,
        count(1) over() total
    </sql>

    <select id="queryElaTraceInfoList" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        with r as
        (select e.agent_no, e.product_name, e.id_customer, e.response_time, max(e.updated_date) upd
        from ela_trace_info e
        <if test='insMobile != null or insName != null or (groupNo != null and groupNo != "A")'>
            ,ela_trace_customer_info c
        </if>
       where e.agent_no = #{agentNo}
         and e.id_customer is not null
        <if test='insMobile != null or insName != null or (groupNo != null and groupNo != "A")'>
            and e.id_customer = c.id_customer
        </if>
        <if test="documentStatus != null">
            and e.document_status = #{documentStatus}
        </if>
        <if test="insName != null">
            and c.ins_name
            <choose>
                <when test="insName == ''">
                    is null
                </when>
                <otherwise>
                    = #{insName}
                </otherwise>
            </choose>
        </if>
        <if test="insMobile != null">
            and c.ins_mobile = #{insMobile}
        </if>
        <choose>
            <when test='groupNo == "X"'>
                and not exists (select null from customer_groups cg where cg.openid = c.openid)
            </when>
            <when test='groupNo != null and groupNo != "A"'>
                and exists (select null from customer_groups cg where cg.openid = c.openid and cg.group_infotype_no =#{groupNo})
            </when>
        </choose>
        group by e.agent_no, e.product_name, e.id_customer, e.response_time
        order by
        <choose>
            <when test="orderBy == 1">
                (select c.ins_name from ela_trace_customer_info c where c.id_customer = e.id_customer) collate "zh_CN" nulls first
            </when>
            <when test="orderBy == 2">
                e.response_time desc nulls first, upd desc
            </when>
            <otherwise>
                upd desc
            </otherwise>
        </choose>
        limit #{pageSize} offset #{pageSize} * (#{currentPage} - 1))
        select
        <include refid="select_column"/>
        from ela_trace_info e,r,
             ela_trace_customer_info c
       where c.id_customer = e.id_customer
         and e.agent_no = r.agent_no
         and e.id_customer = r.id_customer
         and e.product_name = r.product_name
         and e.updated_date = r.upd
         order by
         <choose>
            <when test="orderBy == 1">
                c.ins_name collate "zh_CN" nulls first
            </when>
            <when test="orderBy == 2">
                e.response_time desc nulls first, r.upd desc
            </when>
            <otherwise>
                r.upd desc
            </otherwise>
         </choose>;
    </select>

    <select id="queryElaTraceInfoDetail" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        select
        <include refid="select_column"/>
          from ela_trace_info e,
               ela_trace_customer_info c
         where e.id_customer = c.id_customer
           and c.ins_mobile = #{insMobile}
           and e.agent_no = #{agentNo}
        <if test="openid != null">
           and c.openid = #{openid}
        </if>
        <if test="isLastWeek == 1">
           and e.updated_date > now() - interval '7 day'
        </if>
        order by e.updated_date desc;
    </select>

    <select id="queryNotRespondingCount" resultType="long">
        SELECT count(1)
        FROM ela_trace_info
        WHERE agent_no = #{agentNo}
          AND document_status = '6'
          AND response_time IS NULL;
    </select>

    <update id="upsertElaTraceCustomerInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo" useGeneratedKeys="true" keyProperty="customerId">
        INSERT INTO ela_trace_customer_info (
            ins_name,
            ins_sex,
            ins_age,
            ins_birthday,
            ins_mobile,
            openid
        ) VALUES (
            <choose>
                <when test="insName != null">
                    #{insName},
                </when>
                <otherwise>
                    '',
                </otherwise>
            </choose>
            <choose>
                <when test="insSex != null">
                    #{insSex},
                </when>
                <otherwise>
                    '',
                </otherwise>
            </choose>
            #{insAge},
            #{insBirthday},
            <choose>
                <when test="insMobile != null">
                    #{insMobile},
                </when>
                <otherwise>
                    '',
                </otherwise>
            </choose>
            <choose>
                <when test="openid != null">
                    #{openid}
                </when>
                <otherwise>
                    ''
                </otherwise>
            </choose>)
        on conflict(openid, ins_mobile, ins_name, ins_sex)
        do update set
        <if test="insAge != null">
            ins_age = #{insAge},
        </if>
        <if test="insBirthday != null">
            ins_birthday = #{insBirthday},
        </if>
        updated_by = user,
        updated_date = now();
    </update>

    <insert id="addElaTraceInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        insert into ela_trace_info (
            order_no,
            agent_no,
            product_name,
            product_code,
            document_status,
            response_time,
            source,
            money,
            id_customer,
            policyNo,
            insidtype,
            insidnumber,
            beinsidtype,
            beinsidnumber
        ) values (
            #{orderNo},
            #{agentNo},
            #{productName},
            #{productCode},
            #{documentStatus},
            #{responseTime},
            #{source},
            #{money},
            #{customerId},
            #{policyNo},
            #{insidtype},
            #{insidnumber},
            #{beinsidtype},
            #{beinsidnumber}
        );
    </insert>

    <update id="updateResponseTime" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        UPDATE ela_trace_info
        SET response_time = #{responseTime}, updated_by = user, updated_date = now()
        WHERE id_ela = #{id};
    </update>

    <select id="queryReadAndShareCount" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        with m as(select sit.item_no,sit.item_name from sales_item_table sit where sit.parent_item_no='0501' order by sit.item_no desc),
             n as(select cti.openid,cti.info_type_no,cti.share_type,cti.created_date from customer_trace_info cti where cti.openid=#{openid})
          select item_no "groupNo",item_name "groupName",coalesce(read_total,0) "readTotal",coalesce(share_total,0) "shareTotal" from(
             select m.item_no,
                 m.item_name,
                 (select count(1)
                  from n
                  where n.info_type_no = m.item_no
                        <if test="isLastWeek == 0">
                          and n.created_date > CURRENT_DATE - INTERVAL '2 month'
                        </if>
                  group by n.openid, n.info_type_no
                 ) "read_total",
                 (select count(1)
                  from n
                  where n.info_type_no = m.item_no
                        <![CDATA[ and coalesce(n.share_type, '') <> '' ]]>
                        <if test="isLastWeek == 0">
                            and n.created_date > CURRENT_DATE - INTERVAL '2 month'
                        </if>
                  group by n.openid, n.info_type_no
                 ) "share_total"
             from m
         ) t;
    </select>

    <select id="queryCustomerGroups" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        SELECT
            t2.group_name groupName,
            t1.created_date createdDate,
            t1.group_infotype_no groupNo
        FROM
            customer_groups t1,
            customer_groups_dict t2
        WHERE
            t1.openid = #{openid}
            AND t1.group_infotype_no = t2.info_type_no
        ORDER BY
            t1.created_date DESC;
    </select>

    <sql id="old_select_column">
        e.id_ela id,
        e.order_no  orderNo,
        e.agent_no  agentNo,
        e.product_name productName,
        e.product_code productCode,
        e.ins_name insName,
        substring(e.ins_name FROM char_length(e.ins_name) FOR char_length(e.ins_name)) insLastChar,
        e.ins_sex  insSex,
        e.ins_age  insAge,
        e.ins_mobile insMobile,
        e.document_status documentStatus,
        e.response_time responseTime,
        e.source source,
        e.openid openid,
        e.money money,
        e.created_by        createdBy,
        e.created_date      createdDate,
        e.updated_by        updatedBy,
        e.updated_date      updatedDate,
        count(1) over() total
    </sql>

    <select id="queryOldElaTraceInfoList" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        with r as
        (select agent_no, product_name, ins_name, ins_mobile, response_time, openid, max(updated_date) upd
        from ela_trace_info eti
        where agent_no = #{agentNo}
        <if test="documentStatus != null">
            and document_status = #{documentStatus}
        </if>
        <if test="insName != null">
            and ins_name
            <choose>
                <when test="insName == ''">
                    is null
                </when>
                <otherwise>
                    = #{insName}
                </otherwise>
            </choose>
        </if>
        and ins_mobile != '暂无'
        <if test="insMobile != null">
            and ins_mobile = #{insMobile}
        </if>
        <choose>
            <when test='groupNo == "X"'>
                and not exists (select null from customer_groups cg where cg.openid = eti.openid)
            </when>
            <when test='groupNo != null and groupNo != "A"'>
                and exists (select null from customer_groups cg where cg.openid = eti.openid and cg.group_infotype_no =#{groupNo})
            </when>
        </choose>
        group by agent_no, product_name, ins_name, ins_mobile, response_time, openid
        order by
        <choose>
            <when test="orderBy == 1">
                ins_name collate "zh_CN" nulls first
            </when>
            <when test="orderBy == 2">
                response_time desc nulls first, upd desc
            </when>
            <otherwise>
                upd desc
            </otherwise>
        </choose>
        limit #{pageSize} offset #{pageSize} * (#{currentPage} - 1))
        select
        <include refid="old_select_column"/>
        from ela_trace_info e, r
        where e.agent_no = r.agent_no
        and e.product_name = r.product_name
        and (e.ins_mobile = r.ins_mobile or e.openid = r.openid)
        and e.updated_date = r.upd
        order by
        <choose>
            <when test="orderBy == 1">
                e.ins_name collate "zh_CN" nulls first
            </when>
            <when test="orderBy == 2">
                e.response_time desc nulls first, r.upd desc
            </when>
            <otherwise>
                r.upd desc
            </otherwise>
        </choose>;
    </select>

    <select id="queryOldElaTraceInfoDetail" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        select
        <include refid="old_select_column"/>
        from ela_trace_info e
        where agent_no = #{agentNo}
        and ins_mobile = #{insMobile}
        <if test="openid != null">
            and openid = #{openid}
        </if>
        <if test="isLastWeek == 1">
            and updated_date > now() - interval '7 day'
        </if>
        order by updated_date desc;
    </select>

    <insert id="addOldElaTraceInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo">
        insert into ela_trace_info (
        order_no,
        agent_no,
        product_name,
        product_code,
        <if test="insName != ''">
            ins_name,
        </if>
        ins_sex,
        ins_age,
        ins_mobile,
        document_status,
        response_time,
        source
        <if test="openid != ''">
            ,openid
        </if>
        ,money
        ) values (
        #{orderNo},
        #{agentNo},
        #{productName},
        #{productCode},
        <if test="insName != ''">
            #{insName},
        </if>
        #{insSex},
        #{insAge},
        #{insMobile},
        #{documentStatus},
        #{responseTime},
        #{source}
        <if test="openid != ''">
            ,#{openid}
        </if>
        ,#{money}
        );
    </insert>
</mapper>