mysql - 使用嵌套查询进行自连接

Question

大家好，如果可能的话，如何在子查询上进行自连接？

这是我的尝试

SELECT *
    FROM EMPLOYEE
    INNER JOIN
    (SELECT FIRST_NAME, LAST_NAME AS FULLNAME
    FROM EMPLOYEE
    WHERE FIRST_NAME = 'Nancy' AND LAST_NAME = 'Davolio') AS FNAME
    ON EMPLOYEE.FIRST_NAME = .FIRST_NAME AND EMPLOYEE.LAST_NAME = EMPLOYEEE.FULLNAME; 

这是我自引用的数据库

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID     DECIMAL(9)  NOT NULL,
    LASTNAME        VARCHAR(20)     NOT NULL,
    FIRSTNAME       VARCHAR(10)     NOT NULL,
    TITLE       VARCHAR(30),
    TITLE_OF_COURTESY   VARCHAR(25),
    BIRTHDATE       DATE,
    HIREDATE        DATE,
    ADDRESS         VARCHAR(60),
    CITY        VARCHAR(15),
    REGION      VARCHAR(15),
    POSTAL_CODE     VARCHAR(10),
    COUNTRY         VARCHAR(15),
    HOME_PHONE      VARCHAR(24),
    EXTENSION       VARCHAR(4),
    PHOTO       VARCHAR(255),
    NOTES       VARCHAR(2000),
    REPORTS_TO      DECIMAL(9),
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

如果有效，查询应该能够根据员工的名字和姓氏找到员工向谁报告

我使用简单的自连接进行了类似的查询，它可以做到这一点

 SELECT B.FIRSTNAME, B.LASTNAME
FROM EMPLOYEE AS A,
    EMPLOYEE AS B
WHERE A.REPORTS_TO = B.EMPLOYEE_ID  AND A.FIRSTNAME = 'Nancy' AND A.LASTNAME = 'Davolio';

但是，正如前面提到的，这一次我需要做同样的事情，但在子查询上使用 Selfjoin

感谢大家抽出时间。

Answer 1

子查询应选择REPORTS_TO，而不是员工的姓名。

SELECT e.*
FROM EMPLOYEE AS e
JOIN (
    SELECT REPORTS_TO
    FROM EMPLOYEE
    WHERE FIRSTNAME = 'Nancy' AND LASTNAME = 'Davolio') AS f
ON e.EMPLOYEE_ID = f.REPORTS_TO

DEMO