梧桐数据库之查找至少有3名直接下属的经理

一、背景说明

假设在一个公司内部有多个部门，每个部门由经理和员工组成。为了更好地管理和优化组织结构，公司希望找出那些直接管理至少3名员工的经理。这些经理通常承担更多的管理职责，并且对公司运营具有更大的影响力。通过分析这些经理及其下属，公司可以更好地理解组织结构，进行人力资源规划和优化。

二、表结构说明

我们假设有一个 Employee 表来存储所有员工的信息，包括他们的ID、姓名、所属部门以及他们的直接经理的ID。

-- 创建 Employee 表
CREATE TABLE Employee (
    id INT PRIMARY KEY,          -- 员工ID，主键
    name VARCHAR(100) NOT NULL,  -- 员工姓名
    department VARCHAR(100),     -- 所属部门
    managerId INT                -- 直接经理的ID
);

三、表数据插入

为了演示查询，我们将插入一些示例数据到 Employee 表中。

-- 插入示例数据
INSERT INTO Employee (id, name, department, managerId) VALUES
(1, '张三', '市场部', 4),
(2, '李四', '市场部', 4),
(3, '王五', '市场部', 4),
(4, '赵六', '市场部', NULL),
(5, '孙七', '技术部', 8),
(6, '周八', '技术部', 8),
(7, '吴九', '技术部', 8),
(8, '郑十', '技术部', NULL),
(9, '钱十一', '客服部', 12),
(10, '刘十二', '客服部', 12),
(11, '陈十三', '客服部', 12),
(12, '黄十四', '客服部', NULL),
(13, '杨十五', '财务部', 16),
(14, '朱十六', '财务部', 16),
(15, '秦十七', '财务部', 16),
(16, '尤十八', '财务部', NULL);

四、实现思路分解

1. 统计每个经理的直接下属数量：首先，我们需要计算每个经理（即 managerId）的直接下属数量。
2. 筛选出至少有3名直接下属的经理：然后，我们筛选出那些直接下属数量大于或等于3的经理。
3. 获取这些经理的详细信息：最后，我们从 Employee 表中获取这些经理的详细信息。

五、SQL代码实现

-- 1. 统计每个经理的直接下属数量
WITH ManagerSubordinates AS (
    SELECT 
        managerId,  -- 经理的ID
        COUNT(id) AS subordinates_count  -- 直接下属的数量
    FROM 
        Employee
    WHERE 
        managerId IS NOT NULL  -- 只考虑有经理的员工
    GROUP BY 
        managerId  -- 按经理ID分组
)

-- 2. 筛选出至少有3名直接下属的经理
, ManagersWithThreeOrMoreSubordinates AS (
    SELECT 
        managerId  -- 经理的ID
    FROM 
        ManagerSubordinates
    WHERE 
        subordinates_count >= 3  -- 至少有3名直接下属
)

-- 3. 获取这些经理的详细信息
SELECT 
    e.id,  -- 经理的ID
    e.name,  -- 经理的姓名
    e.department  -- 经理所在的部门
FROM 
    Employee e
JOIN 
    ManagersWithThreeOrMoreSubordinates m  -- 与筛选出至少有3名直接下属的经理的子查询做连接查询
ON 
    e.id = m.managerId;  -- 匹配经理的ID

通过上述步骤，我们可以找到那些至少有3名直接下属的经理，并获取他们的详细信息。这有助于公司在人力资源规划和组织结构优化方面做出更好的决策。

执行结果如下：