如何在CREATE TABLE语句中实现部门在职员工人数自动统计

前提说明 #

现有表结构中Employees表的DepartmentID字段存储员工当前实际任职的部门ID，基于这个关联关系统计各部门当前在职人数。

方案1：单次统计更新 #

如果只是需要一次性批量计算并更新所有部门的在职人数，直接用关联统计的SQL即可：

仅查询统计结果（不更新表） #

SELECT 
    d.DepartmentID,
    d.DepartmentName,
    COUNT(e.EmployeeID) AS CurrentEmployeesCount
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName

直接更新Departments表的字段值 #

UPDATE Departments d
SET CurrentEmployeesCount = (
    SELECT COUNT(*) 
    FROM Employees e 
    WHERE e.DepartmentID = d.DepartmentID
)

注：如果业务中有离职/停用员工的状态区分，需要在Employees表新增IsActive标识字段，统计时增加WHERE IsActive = 1的过滤条件即可。

方案2：自动维护字段值（长期同步） #

如果需要每次员工新增、调岗、离职时自动更新部门人数，不需要手动执行统计，建议给Employees表加触发器实现：

新增员工触发部门人数加1 #

DELIMITER //
CREATE TRIGGER trg_employee_insert AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    UPDATE Departments 
    SET CurrentEmployeesCount = CurrentEmployeesCount + 1 
    WHERE DepartmentID = NEW.DepartmentID;
END //
DELIMITER ;

删除员工触发部门人数减1 #

DELIMITER //
CREATE TRIGGER trg_employee_delete AFTER DELETE ON Employees
FOR EACH ROW
BEGIN
    UPDATE Departments 
    SET CurrentEmployeesCount = CurrentEmployeesCount - 1 
    WHERE DepartmentID = OLD.DepartmentID;
END //
DELIMITER ;

员工调岗触发新旧部门人数更新 #

DELIMITER //
CREATE TRIGGER trg_employee_update AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.DepartmentID <> NEW.DepartmentID THEN
        -- 原部门人数减1
        UPDATE Departments 
        SET CurrentEmployeesCount = CurrentEmployeesCount - 1 
        WHERE DepartmentID = OLD.DepartmentID;
        -- 新部门人数加1
        UPDATE Departments 
        SET CurrentEmployeesCount = CurrentEmployeesCount + 1 
        WHERE DepartmentID = NEW.DepartmentID;
    END IF;
END //
DELIMITER ;

以上触发器语法以MySQL为例，其他数据库可根据对应语法调整，逻辑通用。

内容的提问来源于stack exchange，提问作者Moon