浙江03工作日志按人员查询 sql

with a as (
select acm.name username,
       count(worklog.logtypeid) totallog,
       sum(decode(worklog.logtypeid,'ec229560-f915-449d-ae35-3f60903c9197',1,0)) dailylog,
       sum(decode(worklog.logtypeid,'75782b36-e415-4fe9-ad3b-82a17235e475',1,0)) weeklog,
       sum(decode(worklog.logtypeid,'5bfa76f4-c677-4b4f-825b-783ec88bce79',1,0)) monthlog,
       sum(decode(worklog.logtypeid,'e6f2edf8-a737-41c2-b478-21f60498584d',1,0)) quarterlog,
       sum(decode(worklog.businesstypeid,'63f61fc0-cc19-4036-b19c-57be635887da',1,0))overtime,
       sum(case when log_eva.evaluate >85 then 1 else 0 end) exccelent,
       sum(case when log_eva.evaluate between 76 and 85 then 1 else 0 end) good,
       sum(case when log_eva.evaluate between 50 and 75 then 1 else 0 end) pass,
       sum(case when log_eva.evaluate >50 then 1 else 0 end) nopass  
  from acm_user acm
 inner join (select t.id ,t.name,t.parent_id from acm_department t
                   start with t.id = '37c70b89-4430-470c-93b6-6bf6004ded29'
                   connect by prior t.id = t.parent_id) dept 
    on acm.dept_id = dept.id
 left join itil_worklog worklog
        on worklog.creator = acm.id
 left join ( select logid,avg(decode(evaluate,'优秀',100,'良好',80,'合格',60,'不合格',40)) evaluate
               from itil_logaudit_relation
              group by logid ) log_eva
       on worklog.id = log_eva.logid          

       where worklog.createtime between to_date('2012-07-07 00:00','yyyy-mm-dd hh24:mi:ss') 
                                and to_date('2012-09-07 23:59','yyyy-mm-dd hh24:mi:ss')
                                 or worklog.createtime is null 
                             
   group by acm.name 
   order by totallog desc
)  
   
select acm.name,
       nvl(a.totallog,0) totallog,
       nvl(a.dailylog,0) dailylog,
       nvl(a.weeklog,0) weeklog ,
       nvl(a.monthlog,0) monthlog, 
       nvl(a.quarterlog,0) quarterlog, 
       nvl(a.overtime,0) overtime, 
       nvl(a.exccelent,0) exccelent, 
       nvl(a.good,0) good, 
       nvl(a.pass,0) pass,
       nvl(a.nopass,0) nopass
  from a  
  right join acm_user acm on a.username = acm.name 
  inner join (select t.id ,t.name,t.parent_id from acm_department t
                   start with t.id = '37c70b89-4430-470c-93b6-6bf6004ded29'
                   connect by prior t.id = t.parent_id) dept
    on acm.dept_id = dept.id