通用SQL技术需求：创建视图统计各部门未参与本部门项目的员工数

嘿，这个问题其实可以通过两种通用思路来解决，完全不依赖特定数据库，我给你拆解下具体实现：

思路一：总员工数减去参与本部门项目的员工数 #

核心逻辑是先算出每个部门的总员工数，再减去参与了本部门管控项目的员工数（注意要去重，避免一个员工参与多个项目被重复计数），剩下的就是未参与的数量。

假设你的表结构是这样的（如果和你的实际表名/字段名不一样，替换成自己的就行）：

• departments：部门表，包含dept_id（部门ID）、dept_name（部门名称）
• employees：员工表，包含emp_id（员工ID）、dept_id（所属部门ID）
• projects：项目表，包含proj_id（项目ID）、dept_id（管控该项目的部门ID）
• employee_projects：员工-项目关联表，包含emp_id、proj_id

对应的视图创建SQL：

CREATE VIEW dept_unassigned_employees AS
SELECT
    d.dept_name,
    -- 用COALESCE处理NULL，确保没有员工/没有参与项目时显示0
    COALESCE(total_emp.total, 0) - COALESCE(assigned_emp.assigned_count, 0) AS unassigned_count
FROM departments d
-- 左连接获取每个部门的总员工数
LEFT JOIN (
    SELECT dept_id, COUNT(*) AS total
    FROM employees
    GROUP BY dept_id
) total_emp ON d.dept_id = total_emp.dept_id
-- 左连接获取参与本部门项目的员工数（去重）
LEFT JOIN (
    SELECT e.dept_id, COUNT(DISTINCT e.emp_id) AS assigned_count
    FROM employees e
    JOIN employee_projects ep ON e.emp_id = ep.emp_id
    JOIN projects p ON ep.proj_id = p.proj_id
    WHERE p.dept_id = e.dept_id -- 只统计参与本部门管控项目的员工
    GROUP BY e.dept_id
) assigned_emp ON d.dept_id = assigned_emp.dept_id;

思路二：直接筛选未参与本部门项目的员工 #

另一种思路是先找出所有参与了本部门项目的员工ID，再通过左连接筛选出不在这个列表里的员工，最后按部门统计数量。

对应的SQL：

CREATE VIEW dept_unassigned_employees AS
SELECT
    d.dept_name,
    -- 统计未参与的员工数，没有员工时显示0
    COALESCE(COUNT(DISTINCT e.emp_id), 0) AS unassigned_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
-- 左连接到「参与本部门项目的员工」集合
LEFT JOIN (
    SELECT ep.emp_id
    FROM employee_projects ep
    JOIN projects p ON ep.proj_id = p.proj_id
    JOIN employees e ON ep.emp_id = e.emp_id
    WHERE p.dept_id = e.dept_id
) assigned_emps ON e.emp_id = assigned_emps.emp_id
-- 筛选出没有匹配到参与记录的员工
WHERE assigned_emps.emp_id IS NULL
GROUP BY d.dept_name;

关键注意点 #

• 一定要用LEFT JOIN：确保即使某个部门没有员工，或者没有员工参与项目，也能在结果中显示该部门，不会被过滤掉。
• 必须去重（COUNT(DISTINCT)）：避免同一个员工因为参与多个本部门项目被重复统计。
• 用COALESCE处理NULL：当部门没有员工，或者没有员工参与项目时，保证结果是0而不是NULL，让视图数据更规整。

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