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 ?