Oracle - 具有复杂用户结构的递归SQL

Question

我有复杂的层次结构，一个人可以是团队的团队负责人，也可以是其他团队的团队成员。下面我在sql中创建了简单的结构：

CREATE TABLE USERR (
  ID INTEGER NOT NULL,
  NAME VARCHAR(100)  
);
Create TABLE TEAM(
  ID INTEGER NOT NULL,
  NAME VARCHAR(100)  
);
CREATE TABLE ROLE_TYPE(
  ID INTEGER NOT NULL,
  NAME VARCHAR(100)
);
CREATE TABLE ROLEE(
  ID INTEGER NOT NULL,
  NAME VARCHAR(100),
  ROLE_TYPE_ID INTEGER
);
CREATE TABLE POSITIONN(
  ID INTEGER NOT NULL,
  TEAM_ID INTEGER,
  USER_ID INTEGER,
  ROLE_ID INTEGER
);
INSERT INTO USERR (ID,NAME) VALUES (1,'User 1');
INSERT INTO USERR (ID,NAME) VALUES (2,'User 2');
INSERT INTO USERR (ID,NAME) VALUES (3,'User 3');
INSERT INTO USERR (ID,NAME) VALUES (4,'User 4');
INSERT INTO USERR (ID,NAME) VALUES (5,'User 5');
INSERT INTO USERR (ID,NAME) VALUES (6,'User 6');
INSERT INTO USERR (ID,NAME) VALUES (7,'User 7');
INSERT INTO USERR (ID,NAME) VALUES (8,'User 8');
INSERT INTO ROLE_TYPE (ID,NAME) VALUES (1,'Leader');
INSERT INTO ROLE_TYPE (ID,NAME) VALUES (2,'Member');
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (1,'Role 1',1);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (2,'Role 1',2);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (3,'Role 2',1);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (4,'Role 2',2);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (5,'Role 3',1);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (6,'Role 3',2);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (7,'Role 4',1);
INSERT INTO ROLEE (ID,NAME, ROLE_TYPE_ID) VALUES (8,'Role 4',2);

INSERT INTO TEAM (ID,NAME) VALUES (1,'Team 1');
INSERT INTO TEAM (ID,NAME) VALUES (2,'Team 2');
INSERT INTO TEAM (ID,NAME) VALUES (3,'Team 3');
INSERT INTO TEAM (ID,NAME) VALUES (4,'Team 4');
INSERT INTO TEAM (ID,NAME) VALUES (5,'Team 5');
-- user 1 is leader of team 1 - role 1
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (1,1,1,1);
-- user 2 is member of team 1  - role 1 
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (2,1,2,2);
-- user 2 is leader of team 2  - role 2 
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (3,2,2,3); 
-- user 3 is member of team 2  - role 2  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (4,2,3,4); 
-- user 3 is leader of team 3  - role 3  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (5,3,3,5);
-- user 4 is member of team 2  - role 2  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (6,2,4,4); 
-- user 5 is member of team 3  - role 3  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (7,3,5,6); 
-- user 5 is leader of team 4  - role 4  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (8,4,5,7); 
-- user 6 is member of team 4  - role 4  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (9,4,6,8); 
-- user 7 is member of team 4  - role 4  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (10,4,7,8); 
-- user 4 is leader of team 5  - role 3
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (11,5,4,5);
-- user 8 is member of team 5  - role 4  
INSERT INTO POSITIONN (ID,TEAM_ID,USER_ID,ROLE_ID) VALUES (12,5,8,6); 

问题是，是否有可能使用 connect by 或 with 结构从中获取层次结构？

我已经尝试过这样的事情，但显然它不起作用，因为我找不到创建层次结构的逻辑条件：

with q(name, id, parent_id, parent_name) as (
    select 
      USERr.name, USERR.id, 
      null as parent_id, null as parent_name 
    from USERR, TEAM, ROLE_TYPE, ROLEE, POSITIONN
    where USERR.ID = POSITIONN.USER_ID 
          AND POSITIONN.TEAM_ID = TEAM.ID 
          AND ROLE_TYPE.ID = ROLEE.ROLE_TYPE_ID 
          AND POSITIONN.ROLE_ID = ROLEE.ID
          AND ROLE_TYPE.NAME = 'Leader'
          AND USERR.id = 1
  union all
    select 
      USERR.name, USERR.id, 
      q.id as parent_id, q.name as parent_name 
    from USERR, TEAM, ROLE_TYPE, ROLEE, POSITIONN, q
    where USERR.ID = POSITIONN.USER_ID 
          AND POSITIONN.TEAM_ID = TEAM.ID 
          AND ROLE_TYPE.ID = ROLEE.ROLE_TYPE_ID 
          AND POSITIONN.ROLE_ID = ROLEE.ID
          AND ROLE_TYPE.NAME = 'Member'
          AND USERR.ID = q.id
)
select * from q

是否有可能收到所有用户（即user3）的结果？所以它应该返回：用户 5、用户 6 和用户 7，但不会返回用户 8，因为它不在用户 4 下？

我想，

如果我想获取这种数据，我需要编写一个函数来做到这一点，我是对的？

Answer 1

最好在职位表（如manager_id列）中获取领导和下属之间的经典亲子关系。我试图做一些东西，但由于这种关系是隐含的，我可能会错过一些东西 - 所以下面的代码只是一个想法：

SQL> SELECT u.name, PRIOR u.name manager  FROM
  2  POSITIONN p
  3  , USERR u
  4  , TEAM t
  5  , ROLEE r
  6  , ROLE_TYPE rt
  7  WHERE p.user_id = u.id
  8  AND p.team_id = t.id
  9  AND p.role_id = r.id
 10  AND r.role_type_id = rt.id
 11  AND (u.name != PRIOR u.name)
 12  START WITH u.name = 'User 3' AND rt.name = 'Leader'
 13  CONNECT BY (PRIOR t.id = t.id AND rt.name = 'Member'
 14  AND PRIOR rt.name = 'Leader') OR
 15  (PRIOR u.id = u.id AND rt.name = 'Leader'
 16   AND PRIOR rt.name = 'Member')
 17  /
NAME                 MANAGER                                                    
-------------------- --------------------                                       
User 5               User 3                                                     
User 6               User 5                                                     
User 7               User 5