在公司碰到一个特别刁钻的需求，一起来围观

在公司遇到一个特别刁钻的需求，一起来围观
现在系统里有一张机构表，结构如下：

--创建机构表
create table department (
  dept_id number(10),   --机构编号
  parent_id number(10),  --上级机构编号
  dept_name varchar2(100),  --机构名称
  class number(10)            --机构层级
);
--1级机构(总公司)
insert into department (dept_id,parent_id,dept_name,class) values (1,0,'上海总公司',1);
insert into department (dept_id,parent_id,dept_name,class) values (2,0,'北京总公司',1);

--2级机构(分公司)
insert into department (dept_id,parent_id,dept_name,class) values (3,1,'上海浦东分公司',2);
insert into department (dept_id,parent_id,dept_name,class) values (4,1,'上海普陀分公司',2);
insert into department (dept_id,parent_id,dept_name,class) values (5,2,'北京中关村分公司',2);
insert into department (dept_id,parent_id,dept_name,class) values (6,2,'北京海淀区分公司',2);

--3级机构(部门)
insert into department (dept_id,parent_id,dept_name,class) values (7,3,'上海浦东分公司销售1部',3);
insert into department (dept_id,parent_id,dept_name,class) values (8,3,'上海浦东分公司销售2部',3);

insert into department (dept_id,parent_id,dept_name,class) values (9,4,'上海普陀分公司销售1部',3);
insert into department (dept_id,parent_id,dept_name,class) values (10,4,'上海普陀分公司销售2部',3);

insert into department (dept_id,parent_id,dept_name,class) values (11,5,'北京中关村分公司销售1部',3);
insert into department (dept_id,parent_id,dept_name,class) values (12,5,'北京中关村分公司销售2部',3);

insert into department (dept_id,parent_id,dept_name,class) values (13,6,'北京海淀区分公司销售1部',3);
insert into department (dept_id,parent_id,dept_name,class) values (14,6,'北京海淀区分公司销售2部',3);
commit;

另外还有一张用户表，结构如下：

--创建用户表
create table usrtbl (
  user_id number(10),
  dept_id number(10),
  user_name varchar2(10)
);

insert into usrtbl (user_id,dept_id,user_name) values (1,3,'陈志强');
commit;

现要求查询出用户所属父机构、当前机构及其所有下属机构，比如用户在第2个层级，那么他只能看到他的上级机构、当前机构和下属所有机构，没有权限看到同级的其他机构。

--查询机构表
select d.dept_id,lpad('-',level*2,'--')||d.dept_name as dept_name from department d
start with d.parent_id = 0
connect by prior d.dept_id = d.parent_id;

各位有没有好办法，提供一个能实现该需求的SQL。 递归子查询
[解决办法]
sql分为两类，一个是查询上级的sql，一个查询下级的sql。用循环去查询。
查询上级/下级的sql不用我说吧，你应该能写出来。
[解决办法]

引用:

现在系统里有一张机构表，结构如下：
SQL code?123456789101112131415161718192021222324252627282930--创建机构表create table department (  dept_id number(10),   --机构编号  parent_id number(10),  --上级机构编号  dept_name v……

ChenZw> SELECT DISTINCT D.DEPT_ID,LPAD('-',LEVEL*2,'--')
[解决办法]
D.DEPT_NAME AS DEPT_NAME FROM DEPARTMENT D,USRTBL A
  2  START WITH D.PARENT_ID = 3 
 
  3  CONNECT BY  D.DEPT_ID = PRIOR D.PARENT_ID
  4  ORDER BY 1
  5  /

   DEPT_ID DEPT_NAME
---------- ------------------------------
         1 ------上海总公司
         3 ----上海浦东分公司
         7 --上海浦东分公司销售1部
         8 --上海浦东分公司销售2部

已选择4行。

已用时间:  00: 00: 00.03

[解决办法]

那就使用UNION吧..

按照3 向上递归  + 按照3 向下递归

xxxx
START WITH D.PARENT_ID = 3
CONNECT BY  D.DEPT_ID = PRIOR D.PARENT_ID
UNION
xxxx
START WITH D.PARENT_ID = 3
CONNECT BY  PRIOR D.DEPT_ID = D.PARENT_ID

 
[解决办法]
查询所有机构，过滤统计机构就可以了，很复杂？
[解决办法]
select d.dept_id,d.dept_name as dept_name from department d    where dept_id=(select parent_id from department where dept_id=3) 
union all
  select d.dept_id,lpad(' ',level*2,'  ')
[解决办法]
d.dept_name as dept_name from department d
start with d.dept_id =3
connect by prior d.dept_id = d.parent_id ;