之前的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) <= T.alarm_value
<if test='param.type == "1"'>
AND 100 - SUM (decr_score) > T.warning_value
</if>
<if test='param.type == "0"'>
AND 100 - SUM (decr_score) < T.warning_value
</if>
</select>
其实在这之前有出现一个很坑的东西,之前的语句中,having是放在GROUP BY 之前的,程序一直在报错sytax error on token,但是偏偏这条SQL在数据库里是已经执行过的没有问题,之后将having放在group by后就ok了