自引用表 SQL 查询

我有一个有四列的表格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 查询来显示特定员工的所有可能的经理。他的初级员工或其他下级员工的姓名不应出现在结果集中。

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

我无法找到解决方案。

Ref: SQLite 的WITH 子句 https://sqlite.org/lang_with.html

您需要一个“递归CTE”（公共表表达式）来遍历组织层次结构。像这样：

Query

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

Result:

| 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      |

Setup:

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')
;

Demo https://www.db-fiddle.com/f/7wD9cq6ec1UbiBC9PFsaM6/0

Query 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

Result:

| 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 |