17,377
社区成员
发帖
与我相关
我的任务
分享
--1.创建测试表
create table t1(depno varchar2(10), depname varchar2(100), pre_depno varchar2(10));
create table t2(empno varchar2(10), empname varchar2(100), depno varchar2(10));
--2.添加测试语句
insert into t1 values('001', '顶级部门',null);
insert into t1 values('010', '部门1','001');
insert into t1 values('020', '部门2','001');
insert into t1 values('030', '部门3','020');
insert into t2 values(1,'赵六','010');
insert into t2 values(2,'李四','020');
insert into t2 values(3,'王五','020');
insert into t2 values(4,'张三','030');
insert into t2 values(5,'钱八','001');
commit;
--3.sql实现
select t1.depname,t2.empname
from t1, t2
where t1.depno = t2.depno
start with t1.pre_depno is null
connect by prior t1.depno = t1.pre_depno
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;
--有两个表,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 = '02'
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;