sql递归查询

--有两个表，A 部门表，存储部门ID 名称 上级部门；B表 存储部门员工表 ID 姓名 部门ID
--现在我希望通过一条sql语句取得所有顶级部门下的员工(包含下级部门的员工)
--drop table dept;
create table dept(
 dept_id varchar(5), 
 up_id varchar(5),
 misc varchar(20),
 primary key (dept_id)
);
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES ('12', '-', '顶级部门2');
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES ('1', '-', '顶级部门');
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES ('01', '1', '部门1');
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES ('02', '1', '部门2');
INSERT INTO DEPT (DEPT_ID, UP_ID, MISC) VALUES ('003', '02', '部门3');
--drop table employ;
create table employ(
 emp_id varchar(6),
 name varchar(20),
 dept_id varchar(5),
 primary key(emp_id)
);
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES ('00001', '钱八', '1');
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES ('00002', '赵六', '01');
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES ('00003', '王五', '02');
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES ('00004', '李四', '02');
INSERT INTO EMPLOY (EMP_ID, NAME, DEPT_ID) VALUES ('00005', '张三', '003');


--查询部门为1-顶级部门下的所有员工
with temp(emp_id,name,dept_id) as (
  select e.emp_id,e.name,e.dept_id from employ e left join dept d on e.dept_id = d.dept_id where e.dept_id = '1' 
  union all 
  select d2.emp_id,d2.name,d2.dept_id from temp t,
  （ select emp_id,name,e.dept_id,d3.up_id from employ e,dept d3 where e.dept_id = d3.dept_id）d2 where t.dept_id = d2.up_id
)
select distinct(emp_id),name,dept_id from temp order by emp_id;