优化了一次sql

最新推荐文章于 2025-12-18 18:23:53 发布

原创最新推荐文章于 2025-12-18 18:23:53 发布 · 327 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#优化 #mybatis #sql #sql-database

本文对比分析了旧版与新版SQL语句的区别，新版SQL取消了子查询并调整了HAVING子句的位置，使得结构更加清晰，提高了执行效率。

之前的sql性能有问题修改下

//OLD
 select * from(select r.ENTERPRISE_ID,
        e.ENTERPRISE_NAME,
        e.ADDR,
        e.REPRESENT,
        l.LEVEL_NAME,
        e.EVAL_YEAR ENTERPRISE_YEAR,
        r.EVAL_YEAR RECORD_YEAR,
        sum(DECR_SCORE) as SUM_DECR_SCORE,
        <!-- 根据总扣分与阈值获得预警类型，1：预警，0：告警 -->
        (case
        <![CDATA[when ((100 - sum(DECR_SCORE)) <= t.alarm_value and]]>
        <![CDATA[(100 - sum(DECR_SCORE)) > t.warning_value) then]]>
        '1'
        <![CDATA[when ((100 - sum(DECR_SCORE)) <= t.warning_value) then]]>
        '0'
        end) type,
        t.warning_value,
        t.alarm_value
        from eece_credit_record r
        inner join eece_enterprise e
        on r.enterprise_id = e.enterprise_id and e.eval_year=#{param.enterpriseYear,jdbcType=VARCHAR}
        inner join eece_level l
        on e.level_id = l.level_id
        join eece_set_threshold t
        on 1 = 1
        <!-- 筛选掉状态的记录 -->
        where r.status = '1'
        group by r.ENTERPRISE_ID,
        e.ENTERPRISE_NAME,
        e.ADDR,
        e.REPRESENT,
        l.LEVEL_NAME,
        e.eval_year,
        r.eval_year,
        t.warning_value,
        t.alarm_value
        )
        <where>
            type in ('1','0')
            <if test="param.enterpriseName != null and param.enterpriseName !=''">
                ENTERPRISE_NAME like '%${param.enterpriseName}%'
            </if>
            <if test="param.type != null and param.type !=''">
                and TYPE=#{param.type,jdbcType=VARCHAR}
            </if>
            <if test="param.enterpriseYear != null and param.enterpriseYear !=''">
                and ENTERPRISE_YEAR=#{param.enterpriseYear,jdbcType=VARCHAR}
            </if>
            <if test="param.warningYear != null and param.warningYear !=''">
                and RECORD_YEAR=#{param.warningYear,jdbcType=VARCHAR}
            </if>
        </where>

新SQL取消了子查询，结构清晰点，不将传入的参数放到SQL中执行，仅仅根据param.type的值执行不同的语句

//NEW
<select id="pageCreditWarning" parameterType="com.upsoft.sep.bp.common.entity.RequestPage"
        resultMap="creditWarningResultMap">
        SELECT
        r.enterprise_id,
        E.enterprise_name,
        E.addr,
        E.represent,
        l.level_name,
        E.eval_year enterprise_year,
        r.eval_year record_year,
        SUM (decr_score) AS sum_decr_score,
        <![CDATA[CASE WHEN ((100 - SUM(decr_score)) <= T.alarm_value AND (100 - SUM(decr_score)) > T.warning_value ) THEN '1' WHEN ((100 - SUM(decr_score)) <= T.warning_value ) THEN '0' END TYPE,]]>
        T.warning_value,
        T.alarm_value
        FROM
        eece_credit_record r
        INNER JOIN eece_enterprise E ON r.enterprise_id = E.enterprise_id
        INNER JOIN eece_level l ON E.level_id = l.level_id
        JOIN eece_set_threshold T ON 1 = 1
        WHERE
        r.status = '1'
        AND E.eval_year = #{param.enterpriseYear,jdbcType=VARCHAR}
        AND r.eval_year = #{param.warningYear,jdbcType=VARCHAR}
        <if test="param.enterpriseName != null and param.enterpriseName !=''">
                and e.ENTERPRISE_NAME like '%${param.enterpriseName}%'
        </if>
        GROUP BY
        r.enterprise_id,
        E.enterprise_name,
        E.addr,
        E.represent,
        l.level_name,
        E.eval_year,
        r.eval_year,
        T.warning_value,
        T.alarm_value 
        HAVING
        100 - SUM (decr_score)  &lt;= T.alarm_value
        <if test='param.type == "1"'>
            AND 100 - SUM (decr_score) &gt; T.warning_value
        </if>
        <if test='param.type == "0"'>
            AND 100 - SUM (decr_score) &lt; T.warning_value
        </if>
    </select>

其实在这之前有出现一个很坑的东西，之前的语句中，having是放在GROUP BY 之前的，程序一直在报错sytax error on token,但是偏偏这条SQL在数据库里是已经执行过的没有问题，之后将having放在group by后就ok了