此生遇到最难的一句sql语句

最新推荐文章于 2025-09-12 18:22:12 发布

原创最新推荐文章于 2025-09-12 18:22:12 发布 · 381 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

工作日志专栏收录该内容

12 篇文章

订阅专栏

本文深入探讨了一个复杂的SQL语句，它使用了内连接、右连接、左连接等操作，对用户行为数据进行了多维度的聚合与判断。包括计算总日志次数、不同类型日志的数量、业务类型、评价等级等关键指标。

此生写的第一个比较有难度sql 语句

select acm.name username,
       count(worklog.logtypeid) totallog,
       sum(case when logtypeid = 'ec229560-f915-449d-ae35-3f60903c9197' then 1 else 0 end) type1,
       sum(case when logtypeid = '5bfa76f4-c677-4b4f-825b-783ec88bce79' then 1 else 0 end) type2,
       sum(case when logtypeid = 'e6f2edf8-a737-41c2-b478-21f60498584d' then 1 else 0 end) type3,
       sum(case when logtypeid = '75782b36-e415-4fe9-ad3b-82a17235e475' then 1 else 0 end) type4,
       sum(case when businesstypeid = '63f61fc0-cc19-4036-b19c-57be635887da' then 1 else 0 end) overtime,
       sum(case when evaluate = '优秀' then 1 else 0 end) exccelent,
       sum(case when evaluate = '良好' then 1 else 0 end) good,
       sum(case when evaluate = '合格' then 1 else 0 end) pass,
       sum(case when evaluate = '不合格' then 1 else 0 end) nopass
  from acm_user acm
 inner join acm_department dept
    on acm.dept_id = dept.id
 right join itil_worklog worklog
        on worklog.creator = acm.id
 inner join itil_logaudit_relation logaudit
       on worklog.id = logaudit.logid 
 left join ( select
       logid,
       case when avg(decode(evaluate,'优秀',100,'良好',80,'合格',60,'不合格',40)) > 85 then '优秀'
            when avg(decode(evaluate,'优秀',100,'良好',80,'合格',60,'不合格',40)) between 71 and 85 then '良好'
            when avg(decode(evaluate,'优秀',100,'良好',80,'合格',60,'不合格',40)) between 56 and 71 then '合格'
            else '不合格' end evaluate
  from itil_logaudit_relation
 group by logid ) log_eva
       on logaudit.logid = log_eva.logid 
       where worklog.createtime >= to_date('2012-3-28 16:56:47','yyyy-mm-dd hh24:mi:ss') 
       and worklog.createtime < to_date('2012-7-10 16:56:47','yyyy-mm-dd hh24:mi:ss') and dept.id = 'e57984e5-f2f0-4a2d-8ba0-185770a81f1e' 
       group by acm.name