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
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
看起来应该还是比较复杂 一会看看有没有办法优化