sql - ORACLE条件选择多行

Question

有这样的表:

CREATE TABLE Emp
    (manager varchar(14), sub varchar(14))
;

INSERT ALL 
    INTO Emp (manager, sub)
         VALUES ('boss', 'john')
    INTO Emp (manager, sub)
         VALUES ('boss', 'eric')
    INTO Emp (manager, sub)
         VALUES ('boss', 'anna')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'boss')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'kris')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'dave')
SELECT * FROM dual
;

并给出了一个员工的名字(子)，我希望返回的名字如下:

• 如果员工是经理 - 让他自己和他的团队
• 如果员工不是经理 - 找他的经理和他的团队

这个只有当员工是经理时才有效:

SELECT sub FROM Emp
WHERE manager = 'boss' OR sub ='boss'
;

所以基本上，如果给定的员工是“john”或“boss”，数据应该是这样的:

john
eric
anna
boss

Answer 1

  select manager from emp where sub in (:name) and manager in (select sub from emp) 
 union
select sub from emp where manager in (select manager from emp where sub=:name and sub=manager) 
 union 
select sub from emp where manager in (select :name from emp )
 union
select sub from emp where manager in (select manager from emp where sub=:name and manager in (select sub from emp))
 union
select :name from dual;

这里的名字是绑定(bind)变量。我试过约翰和老板。它对我有用。它给了我你预期的数据。