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;