自引用表SQL查询

Question

我有一个具有四列的表格：id、name、designation、manager_id。

表格模式：

CREATE TABLE "Employee_Information" 
(
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    "name" varchar, 
    "designation" varchar, 
    "manager_id" integer references employee_information(id)
);

它是如下的。

ID  Name    Designation   Manager_id
-------------------------------------
1   Raja    CEO 
2   Mani    CTO           1
3   Kavi    COO           1
4   Murugan Head          3
5   Alpha   Head(Fin)     4
7   Kannan  Head          4

员工等级如下：

Raja CEO
    Mani CTO
    Kavi COO
               Murugan Head
                       Alpha Head(Fin)
                           Kannan Head 
       Beta CFO
       Delta Head 

我需要一条SQL查询语句，来显示特定员工可能的所有经理。他的下属或其他子级员工的姓名不应出现在结果集中。

显示同级别或以上的所有其他员工。

我无法想出解决方法。

- Aravind

Answer 1

参考: SQLite WITH clause

你需要使用“递归公共表达式”（common table expression，CTE）来遍历组织层次结构。像这样:

查询

WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name)
AS (
    SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar)
    FROM Employee_Information
    WHERE Manager_ID IS NULL
    UNION ALL
        SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
        FROM Employee_Information e
        INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
    )
SELECT *
FROM Emp_CTE

结果：

| ID |  Name   | Designation | Manager_id | Manager_name |
|----|---------|-------------|------------|--------------|
|  1 | Raja    | CEO         | null       | null         |
|  3 | Kavi    | COO         | 1          | Raja         |
|  2 | Mani    | CTO         | 1          | Raja         |
|  4 | Murugan | Head        | 3          | Kavi         |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      |
|  7 | Kannan  | Head        | 4          | Murugan      |

设置：

CREATE TABLE "Employee_Information" ("id" INTEGER PRIMARY KEY AUTOINCREMENT 
NOT NULL, "name" varchar, "designation" varchar, "manager_id" integer references employee_information(id));



INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (1, 'Raja', 'CEO', NULL)
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (2, 'Mani', 'CTO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (3, 'Kavi', 'COO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (4, 'Murugan', 'Head', '3')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (5, 'Alpha', 'Head(Fin)', '4')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (7, 'Kannan', 'Head', '4')
;

演示

查询 2

WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name, namepath)
AS (
    SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar), name as namepath
    FROM Employee_Information
    WHERE Manager_ID IS NULL
    UNION ALL
        SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
  , Emp_CTE.namepath || '/' || e.Name 
        FROM Employee_Information e
        INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
    )
SELECT *
FROM Emp_CTE

结果：

| ID |  Name   | Designation | Manager_id | Manager_name |         namepath         |
|----|---------|-------------|------------|--------------|--------------------------|
|  1 | Raja    | CEO         | null       | null         | Raja                     |
|  3 | Kavi    | COO         | 1          | Raja         | Raja/Kavi                |
|  2 | Mani    | CTO         | 1          | Raja         | Raja/Mani                |
|  4 | Murugan | Head        | 3          | Kavi         | Raja/Kavi/Murugan        |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      | Raja/Kavi/Murugan/Alpha  |
|  7 | Kannan  | Head        | 4          | Murugan      | Raja/Kavi/Murugan/Kannan |

- Paul Maxwell

---

如果两个记录相互指向，将会创建一个无限循环。但结果会是什么？溢出吗？ - Rafe

它可能会产生无限循环 - 但是CTE将报告错误并停止（具体如何和何时取决于DBMS）。 - Paul Maxwell

Answer 2

下面的查询将起作用：

SELECT Name+' '+Designation AS 'Manager' FROM table1 WHERE ID=(SELECT manager_id FROM table1 WHERE ID='<employee_id>')

如果您通过其他语言执行此查询，只需传递保存有employee_id的变量即可。

- Sabarish Ramachandran

---

我得到的默认值是0。这是CEO的ID。对于employee_id的所有值都得到相同的答案。 - Aravind

不可能的，因为当employee_id是3和4时，manager_id将与子查询不同，这将获取该子查询结果的manager_id的详细信息。 - Sabarish Ramachandran

它需要“递归”（可通过“with cause”获得） - Paul Maxwell