此生写的第一个比较有难度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 ?
?
?