比较值得学习的sql语句_decode,to_char,TO_CHAR(sysdate,'MM'),(t.end_date,'HH24')>=21

select t1.*,t2.start_date,t2.status from(

select e.user_id,e.emp_name,o.name,count(*) from t_app_work_extra t

left join m_employee e on t.proposer=e.user_id left join m_organization o on e.dept_id=o.org_id

where to_char(t.end_date,'HH24')>=21 andto_char(t.start_date,'yyyy-MM-dd')>='2011-11-26' and to_char(t.end_date,'yyyy-MM-dd')<='2012-01-25' group by e.user_id,e.emp_name,o.name)    t1

left join(

select e.user_id,t.start_date,decode(t.status,'C','审批通过','P','审批中','N','未提交','D','审批未通过')as status,

count(*) from t_app_work_extra t left join m_employee e on t.proposer=e.user_id left join m_organization o on e.dept_id=o.org_id

where to_char(t.end_date,'HH24')>=21 and to_char(t.start_date,'yyyy-MM-dd')>='2011-11-26' and to_char(t.end_date,'yyyy-MM-dd')<='2012-01-25' group by e.user_id,t.start_date,t.status

) t2 on t1.user_id=t2.user_id

SELECT count(*),
  E.EMP_NAME,
  M.name
from M_EMPLOYEE E
left join M_ORGANIZATION M on M.ORG_ID                    =E.DEPT_ID
left join T_ECM_BIRTH_BLESS t on T.TO_USER=E.USER_ID  

WHERE TO_CHAR(E.BIRTHDAY,'MM')>=TO_CHAR(sysdate,'MM')group by E.EMP_NAME