mysql - 如果部门有超过 20 名员工，则返回部门中最年长员工的信息

Question

不要评判我，我是 SQL 查询新手。我得到了方案，如下图所示。因此，有 2 个表，第一个雇员包含 EmployeeID、FirstName、LastName、DateOfBirth 和 DepartmentID。第二个称为 Department，包含 DepartmentID 和 DepartmentName 。

我想返回包含超过 20 名员工的每个部门中最年长的员工的名字、姓氏和部门名称。

我的解决方案是以下查询:

SELECT FirstName, LastName, DepartmentName
FROM employees
LEFT JOIN department
ON employees.DepartmentID = department.DepartmentID
    WHERE (employees.DateOfBirth = 
(SELECT MIN(employees.DateOfBirth ) FROM (
SELECT *FROM employees WHERE employees.DepartmentID IN ( 
SELECT employees.DepartmentID FROM employees GROUP BY DepartmentID HAVING COUNT(*) > 20)));

我认为这个逻辑很好，因为内部 SELECT 语句将返回拥有超过 20 名员工的每个部门的 ID，而外部应该返回最旧的员工。

我遇到的问题是，当我尝试执行此查询时，它返回SQL 错误 每个派生表必须有自己的别名。我尝试在每个派生表上添加别名，但结果仍然相同。请帮我解决这个问题。

此外，如果有人有不同的解决方案，请分享。

谢谢。草莓要求的添加，创建查询

CREATE TABLE Employees
(
EmployeeID int, 
FirstName varchar(10),
LastName varchar(15),
DateOfBirth date,
DeparmentID int
)

CREATE TABLE Department
(
DepartmentID int, 
DepartmentName varchar(15)
)

Answer 1

由于格式不一致，您的查询很难阅读。所以我将按如下方式清理它:

SELECT  FirstName, LastName, DepartmentName
FROM    employees
        LEFT JOIN department
        ON employees.DepartmentID = department.DepartmentID
WHERE   (employees.DateOfBirth = 
        (
        SELECT  MIN(employees.DateOfBirth)
        FROM    (
                SELECT  *
                FROM    employees
                WHERE   employees.DepartmentID IN (
                        --Departments with more than 20 employees
                        SELECT  employees.DepartmentID
                        FROM    employees
                        GROUP BY DepartmentID
                        HAVING COUNT(*) > 20)
                ) -- You need an alias here.
                  -- Also from this point you were missing closing brackets.

您的查询存在问题:

• 显然，缺少别名和右括号意味着您甚至无法测试您的查询。
• 此外，SELECT MIN(employees.DateOfBirth) 仅返回单个值。不是每个部门的值。
• 因此，您的总体结果仅包括所有“大”部门中最年长的员工。 (除非每个部门中年龄最大的员工碰巧出生日期相同。)
• 如果任何员工碰巧与大部门中最年长的员工出生日期相同，它还可能包括较小部门的结果。而且该员工甚至不必是其部门中最年长的!
• 使用过多的子查询也会导致效率低下。

CTE(通用表表达式)非常适合简化复杂的查询。但不知道mysql是否支持。所以这个解决方案仍然使用子查询。

SELECT  e.FirstName, e.LastName, d.DepartmentName
FROM    employees e -- I prefer short aliases
        INNER JOIN (
        -- This sub-query returns the earliest birth date within each
        -- big department. This needs to be an aliased query so you
        -- can join to other tables for your desired columns.
        SELECT  DepartmentID, MIN(DateOfBirth) AS MinDOB -- Must alias column
        FROM    employees
        WHERE   DepartmentID IN (
                -- Big departments
                SELECT  DepartmentID
                FROM    employees
                GROUP BY DepartmentID
                HAVING COUNT(*) > 20
                )
        GROUP BY DepartmentID
        ) ddob -- Alias Department Date of Birth
        -- As a result of inner joining to ddob your employees
        -- will be filtered to only those that match the relevant
        -- ones identified in the query.
        ON  e.DepartmentID = ddob.DepartmentID
        AND e.DateOfBirth = ddob.MinDOB
        INNER JOIN Department d
        ON  d.DepartmentID = e.DepartmentID

在上述解决方案中需要注意的是，如果 2 名员工在一个部门中资历最老，则两人都将被退回。

此方法在结构上与您的方法相似，但您也可以从另一个方向解决问题。

• 从所有部门开始招募最年长的员工。
• 并且仅在最后根据部门规模过滤结果。

我将这个留给你尝试。我怀疑查询会更简单一些。