梧桐資料庫之查詢至少有3名直接下屬的經理

陈小他發表於2024-11-25
資料庫

一、背景說明

假設在一個公司內部有多個部門，每個部門由經理和員工組成。為了更好地管理和最佳化組織結構，公司希望找出那些直接管理至少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名直接下屬的經理，並獲取他們的詳細資訊。這有助於公司在人力資源規劃和組織結構最佳化方面做出更好的決策。

執行結果如下：

idnamedepartment
4 趙六 市場部
8 鄭十 技術部
12 黃十四 客服部
16 尤十八 財務部

相關文章