HR表查询雇员工作几年几月

conn hr/hr

 

查询雇员工作几年几月

select LAST_NAME,HIRE_DATE,trunc(months_between(sysdate,HIRE_DATE)/12,0) work_year,
trunc(mod(months_between(sysdate,HIRE_DATE),12),0) work_month
from employees;
 

查询雇员1995、1996、1997、1998年上班的人数总和
select count(*) total,sum(decode(to_char(HIRE_DATE,'yyyy'),'1995',1)) year_1995,sum(decode(to_char(HIRE_DATE,'yyyy'),'1996',1)) year_1996,sum(decode(to_char(HIRE_DATE,'yyyy'),'1997',1)) year_1997,sum(decode(to_char(HIRE_DATE,'yyyy'),'1998',1)) year_1998
from employees;