今天写了一条把我两个月职业生涯学到的SQL知识全用上了的语句

本文链接：https://blog.youkuaiyun.com/Fresh_Desp/article/details/78261674

本文介绍了一个信用记录预警系统的查询优化方案，通过调整SQL查询语句来提高查询效率。优化后的查询利用MyBatis框架，增加了参数化设置，并对条件判断进行了细化，包括企业名称、评估年份等，以实现更精准的数据筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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,
       (case
         when ((100 - sum(DECR_SCORE)) <= t.alarm_value and
              (100 - sum(DECR_SCORE)) > t.warning_value) then
          '预警'
         when ((100 - sum(DECR_SCORE)) <= t.warning_value) then
          '预警'
       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 e.eval_year = '2016'
   and r.eval_year = '2017'
 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)) < t.alarm_value

看起来应该还是比较复杂一会看看有没有办法优化

————————————————————————————————————————————

优化：

<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
        LEFT 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}
        AND E.EVAL_ORG_PATH like '%${param.evalOrgPath}%'
        <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>