SQL笔试题-在职员工数量

create table DAO_EMPS
(
  ename      VARCHAR2(10),
  hiredate   DATE,
  hire_years NUMBER
)
;

insert into DAO_EMPS (ename, hiredate, hire_years)
values ('SMITH', to_date('17-12-1980', 'dd-mm-yyyy'), 2);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('ALLEN', to_date('20-02-1981', 'dd-mm-yyyy'), 2);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('WARD', to_date('22-02-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('JONES', to_date('02-04-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('MARTIN', to_date('28-09-1981', 'dd-mm-yyyy'), 3);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('BLAKE', to_date('01-05-1981', 'dd-mm-yyyy'), 2);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('CLARK', to_date('09-06-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('SCOTT', to_date('19-04-1987', 'dd-mm-yyyy'), 2);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('KING', to_date('17-11-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('TURNER', to_date('08-09-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('ADAMS', to_date('23-05-1987', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('JAMES', to_date('03-12-1981', 'dd-mm-yyyy'), 3);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('FORD', to_date('03-12-1981', 'dd-mm-yyyy'), 1);
insert into DAO_EMPS (ename, hiredate, hire_years)
values ('MILLER', to_date('23-01-1982', 'dd-mm-yyyy'), 2);
commit;

select  to_char(everyday,'yyyy-mm-dd')  || ' | ' ||count(hiredate)
  from dao_emps right join 
       (select  (min_hire_date + level) everyday
          from dual,
               (select min(hiredate) min_hire_date,
                       max(add_months(hiredate, hire_years * 12)) -
                       min(hiredate) num_days
                  from dao_emps)
        connect by level <= num_days) nian_li
 on  everyday >= dao_emps.hiredate  and    everyday<=(add_months(hiredate, hire_years * 12)-1 )     
      group by everyday 
       order by everyday