列出拥有最年长员工的经理姓名

Question

我试图选择一个拥有最年长员工的经理的名字和姓氏，但是我遇到了一些问题，因为每当我运行这个：

SELECT PERSONAL_DETAILS.FIRST_NAME, PERSONAL_DETAILS.SURNAME, MIN(STAFF.join_date) AS Join_Date
FROM STAFF

INNER JOIN PERSONAL_DETAILS
ON STAFF.PERSONAL_ID = PERSONAL_DETAILS.PERSONAL_ID

WHERE STAFF.ROLE_NAME = 'staff'
GROUP BY PERSONAL_DETAILS.FIRST_NAME, PERSONAL_DETAILS.SURNAME

因为where子句设置为查找所有员工，所以它返回所有员工，如果我将其更改为manager，则返回所有经理。我理解为什么会这样，但我真的不知道如何解决我的问题。

我希望结果集返回经理的名字和姓氏以及他们最年长的员工的日期。

我觉得这与我的表是如何设置的无关，因为在我的情况下我无法更改它们，更重要的是我如何编写一条SQL语句，当涉及多个表时，该语句允许我选择/匹配一个名称和最旧的日期，因为我需要将staff表加入到personal_细节中，例如获取经理的名字和姓氏，但我需要这些名字与最早的日期匹配。

Answer 1

试试这个

SELECT P.FIRST_NAME, P.SURNAME, X.join_date AS Join_Date
FROM PERSONAL_DETAILS P
INNER JOIN 
(SELECT STAFF.PERSONAL_ID,STAFF.JOIN_DATE FROM STAFF WHERE STAFF.ROLE_NAME = 'STAFF' AND JOIN_DATE = (SELECT MIN(JOIN_DATE) FROM STAFF WHERE STAFF.ROLE_NAME = 'STAFF')
) X On X.PERSONAL_ID = P.PERSONAL_ID

第一步：获取最小加入日期第二步：从第一步获取员工详细信息第三步：从第二步获取经理详细信息

Answer 2

这里不需要聚合。按顺序思考，只取前1行：

SELECT pd.FIRST_NAME, pd.SURNAME, s.join_date
FROM STAFF s INNER JOIN
     PERSONAL_DETAILS pd
     ON s.PERSONAL_ID = pd.PERSONAL_ID
WHERE s.ROLE_NAME = 'staff'
ORDER BY s.join_date
FETCH FIRST 1 ROW ONLY;

（这适用于Oracle 12c。在早期版本中，您需要一个子查询。）

在展示了这一点之后，我怀疑它是否符合你的意愿。我希望这是员工的名字，而不是他们的经理。你可以找到答案。

Answer 3

让我们试着逐段构建。首先，看起来你在寻找一个或多个在公司工作时间最长的人，而不是最老的员工。我认为这是基于这样一个事实，即您使用MIN（JOIN\u DATE）来确定要选择的员工，而不是例如MIN（BIRTH\u DATE）。那么，好的，为了找到最早的JOIN\u日期，我们使用了MIN函数，正如您已经做的那样：

SELECT MIN(JOIN_DATE)
  FROM STAFF
  WHERE ROLE_NAME = 'staff'

太好了，这为所有“员工”人员（而不是“经理”人员）提供了最早的加入日期。现在，要查找具有该JOIN_日期的一个或多个人员，我们使用上面的子查询：

SELECT *
  FROM STAFF emp
  WHERE ROLE_NAME = 'staff' AND
        JOIN_DATE = (SELECT MIN(JOIN_DATE)
                       FROM STAFF
                       WHERE ROLE_NAME = 'staff')

好的，现在我们有了在公司工作时间最长的员工。现在，为了找到他们的经理（根据您的评论），我们将使用员工的BRANCH_ID来找到经理：

SELECT *
  FROM STAFF mgr
  WHERE mgr.ROLE_NAME = 'manager' AND
        mgr.BRANCH_ID IN (SELECT DISTINCT BRANCH_ID
                            FROM STAFF emp
                            WHERE ROLE_NAME = 'staff' AND
                                  JOIN_DATE = (SELECT MIN(JOIN_DATE)
                                                 FROM STAFF
                                                 WHERE ROLE_NAME = 'staff')

现在我们必须进入PERSONAL\u DETAILS表以查找经理的姓名，因此我们将把PERSONAL\u DETAILS加入到上述查询中

SELECT pd.FIRST_NAME, pd.SURNAME
  FROM STAFF mgr
  INNER JOIN PERSONAL_DETAILS pd
    ON pd.PERSONAL_ID = mgr.PERSONAL_ID
  WHERE mgr.ROLE_NAME = 'manager' AND
        mgr.BRANCH_ID IN (SELECT DISTINCT BRANCH_ID
                            FROM STAFF emp
                            WHERE ROLE_NAME = 'staff' AND
                                  JOIN_DATE = (SELECT MIN(JOIN_DATE)
                                                 FROM STAFF
                                                 WHERE ROLE_NAME = 'staff')

现在，坦率地说，所有这些子查询等都给我带来了蜂窝和气体，让我的手因所有缩进而酸痛，所以让我们介绍一些新的东西：公共表表达式（CTE）。CTE是在SELECT之前定义的，基本上是一个小表，可以像主查询中的任何其他表一样使用它：

WITH MIN_STAFF_JOIN_DATE AS (SELECT MIN(JOIN_DATE) AS MIN_JOIN_DATE
                               FROM STAFF
                               WHERE ROLE_NAME = 'staff'),
     EARLIEST_EMPLOYEES AS (SELECT s.*, d.FIRST_NAME, d.SURNAME
                              FROM STAFF s
                              INNER JOIN PERSONAL_DETAILS d
                                ON d.PERSONAL_ID = s.PERSONAL_ID
                              INNER JOIN MIN_STAFF_JOIN_DATE msjd
                                ON msjd.MIN_JOIN_DATE = s.JOIN_DATE
                              WHERE s.ROLE_NAME = 'staff'),
     MANAGERS AS (SELECT m.*, d.FIRST_NAME, d.SURNAME
                    FROM STAFF m
                    INNER JOIN PERSONAL_DETAILS d
                      ON d.PERSONAL_ID = m.PERSONAL_ID
                    WHERE m.ROLE_NAME = 'manager')
SELECT ee.FIRST_NAME AS EMPLOYEE_FIRST_NAME,
       ee.SURNAME AS EMPLOYEE_SURNAME,
       ee.JOIN_DATE,
       mgr.FIRST_NAME AS MANAGER_FIRST_NAME,
       mgr.SURNAME AS MANAGER_SURNAME
  FROM EARLIEST_EMPLOYEES ee
  INNER JOIN MANAGERS mgr
    ON mgr.BRANCH_ID = ee.BRANCH_ID