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

原创于 2017-08-18 18:39:45 发布 · 340 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#菜鸟工作日记 #sql

本文介绍了一个复杂的SQL查询案例，该查询涉及多个表的联接操作、分组统计及条件筛选等高级特性，用于评估企业的信用状况。

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

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