SQL查詢部門經理及人員變動統計的實現思路

多表關聯與條件聚合實現部門月度人事報表

需求：查詢部門名稱、部門經理姓名、1月份入職人數、離職人數、外派人數。涉及部門表、員工表、異動記錄表多表關聯與條件統計。

表結構假設

-- 部門表
dept (dept_id, dept_name, manager_id)  -- manager_id 關聯 emp.emp_id

-- 員工表
emp (emp_id, emp_name, dept_id, hire_date, status)

-- 異動記錄表
emp_change (change_id, emp_id, change_type, change_date, dept_id)
-- change_type: 'HIRE'入職, 'LEAVE'離職, 'DISPATCH'外派

核心SQL（以MySQL為例）

SELECT 
    d.dept_name,
    m.emp_name AS manager_name,
    COUNT(CASE WHEN ec.change_type = 'HIRE' AND MONTH(ec.change_date) = 1 THEN 1 END) AS hire_cnt,
    COUNT(CASE WHEN ec.change_type = 'LEAVE' AND MONTH(ec.change_date) = 1 THEN 1 END) AS leave_cnt,
    COUNT(CASE WHEN ec.change_type = 'DISPATCH' AND MONTH(ec.change_date) = 1 THEN 1 END) AS dispatch_cnt
FROM dept d
LEFT JOIN emp m ON d.manager_id = m.emp_id          -- 關聯經理信息
LEFT JOIN emp_change ec ON d.dept_id = ec.dept_id   -- 關聯異動記錄
WHERE ec.change_date >= '2024-01-01' AND ec.change_date < '2024-02-01' -- 1月範圍
GROUP BY d.dept_id, d.dept_name, m.emp_name;

關鍵技術點

1. LEFT JOIN保留無異動部門：即使1月無人事異動，部門也會顯示（計數為0）。
2. 條件聚合：COUNT(CASE WHEN ... THEN 1 END)實現同一查詢多指標統計，避免多次掃表。
3. 日期範圍查詢：用>= '2024-01-01' AND < '2024-02-01'替代MONTH()函數，利用索引提升性能。
4. 經理信息獲取：部門表冗餘manager_id或通過emp表role='MANAGER'關聯。

擴展：動態月份參數化

SET @target_month = '2024-01';
SELECT ...
WHERE ec.change_date >= @target_month 
  AND ec.change_date < DATE_ADD(@target_month, INTERVAL 1 MONTH)
...

性能優化建議

• 在emp_change(change_date, dept_id, change_type)建立聯合索引。
• 大數據量下考慮分區表（按月分區）或預聚合物化視圖。