SQL表结构合理性咨询及同部门员工最多项目查询方法

首先先确认你的表结构是否支持这个需求：只要你的三张表中emp_no的含义是用来关联员工的（比如Employee.emp_no是员工唯一标识，Department.emp_no记录了该员工所属的部门，Project.emp_no记录了参与该项目的员工），那么当前表结构是可以实现这个需求的。不过这里有个小建议：正常来说Department表应该用dept_no/dept_id作为部门唯一标识，Employee表通过dept_no关联到Department，这样表结构会更规范，但基于你目前的设计，我们依然可以写出查询语句。

接下来是具体的查询实现步骤和代码：

步骤分解 #

• 第一步：关联三张表，把项目、员工、部门的关联关系拉通，得到每个项目下员工及其所属部门的完整数据
• 第二步：按项目和部门分组，统计每个项目中来自同一部门的员工数量
• 第三步：找出所有分组里的最大员工数
• 第四步：筛选出员工数等于最大值的项目（可能有多个项目并列第一）

具体SQL代码 #

-- 先统计每个项目中各部门的员工数量
WITH project_dept_counts AS (
    SELECT 
        p.project_id,
        p.project_name,
        d.dept_name, -- 假设你的Department表有dept_name字段，如果是dept_no也可以替换
        COUNT(DISTINCT e.emp_no) AS dept_emp_count
    FROM Project p
    JOIN Employee e ON p.emp_no = e.emp_no
    JOIN Department d ON e.emp_no = d.emp_no
    GROUP BY p.project_id, p.project_name, d.dept_name
),
-- 找出最大的部门员工数量
max_count AS (
    SELECT MAX(dept_emp_count) AS max_num
    FROM project_dept_counts
)
-- 筛选出符合条件的项目
SELECT 
    project_id,
    project_name,
    dept_name,
    dept_emp_count
FROM project_dept_counts, max_count
WHERE dept_emp_count = max_num;

代码解释 #

1. CTE project_dept_counts：通过JOIN关联三张表，按项目和部门分组，用COUNT(DISTINCT e.emp_no)避免同一员工被重复统计（比如如果一个员工在Project表中有多条记录的话）。
2. CTE max_count：计算所有项目-部门组合中的最大员工数。
3. 最后一步：将统计结果和最大数关联，筛选出员工数等于最大值的项目和部门信息。

如果你的表中没有project_id、project_name、dept_name这些字段，只需要替换成你实际的字段名即可。比如如果Project表用project_no作为唯一标识，就把project_id换成project_no。

另外，如果你的Department表中emp_no是部门负责人的编号而不是员工所属部门的关联，那这个表结构就不支持了，这种情况下你需要调整表结构，给Employee表添加dept_no字段关联Department的dept_no。

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