oracle 涉及行转列、复合查询、函数、 HAVING 、order by、group by、count、case when then、 join

//计算用户登录日志

select u.uuid 用戶编号, u.Name 姓名 ,

count(case when l.operatetime is not null then 1 else null end) 登录次数,
count(distinct to_CHAR(l.operatetime,'dd')) 登录天数,
count( case when  to_CHAR(operatetime,'MM')='12' then 1 else null end) 十二月份登录次数
from T_SYS_USER u left join T_SYS_LOG l on u.login_name = l.UserID 
where  l.operatetime>"TO_DATE"('2017-12-04 00:00:00', 'yyyy-mm-dd,hh24:mi:ss')
group by u.uuid,u.Name  
having count(case when l.operatetime is not null then 1 else null end) >= 0 
order by  COUNT(case when l.operatetime is not null then 1 else null end) desc