如何查询分配员工数量最多的项目？附表结构及SQL代码

嘿，我注意到你现在的需求是找出分配员工数量最多的项目，但你当前写的SQL语句其实是在查询所有参与了至少一个项目的员工姓名，和你的目标需求不太匹配哦。让我帮你调整一下方案～

首先先明确你的表结构（我帮你补全了字段间的分隔符，方便阅读）：

-- 员工表
Employee24 (EMPLOYEEID, FIRSTNAME, LASTNAME, GENDER);
-- 项目-员工关联表
PROJECT24 (PROJECTID, PROJECTNAME, EMPLOYEEID);

你当前的SQL语句：

SELECT FIRSTNAME, LASTNAME FROM EMPLOYEE24 E WHERE E.EMPLOYEEID IN ( SELECT L2.EMPLOYEEID FROM PROJECT24 L2 group by l2.employeeid)

作用是筛选出所有至少参与了一个项目的员工，和“找员工最多的项目”完全是两个不同的查询目标。

下面给你两种符合需求的SQL写法，适配不同的数据库环境：

方法一：用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

这种写法简洁直观，还能自动处理多个项目员工数并列最多的情况：

SELECT PROJECTID, PROJECTNAME, employee_count
FROM (
    SELECT 
        p.PROJECTID,
        p.PROJECTNAME,
        COUNT(DISTINCT p.EMPLOYEEID) AS employee_count,
        -- 按员工数降序排名，并列的项目会得到相同的排名
        RANK() OVER(ORDER BY COUNT(DISTINCT p.EMPLOYEEID) DESC) AS rnk
    FROM PROJECT24 p
    GROUP BY p.PROJECTID, p.PROJECTNAME
) ranked_projects
WHERE rnk = 1;

• 内层子查询先统计每个项目的员工数量（用DISTINCT是为了避免同一个员工被重复统计到同一个项目里，如果你的数据里不会出现这种重复分配的情况，可以去掉DISTINCT提升效率）
• 用RANK()窗口函数给每个项目按员工数降序排名
• 外层筛选出排名第一的项目，自动包含所有并列最多的项目

方法二：用子查询兼容老版本数据库 #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用这种嵌套子查询的方式：

SELECT 
    p.PROJECTID,
    p.PROJECTNAME,
    COUNT(DISTINCT p.EMPLOYEEID) AS employee_count
FROM PROJECT24 p
GROUP BY p.PROJECTID, p.PROJECTNAME
HAVING COUNT(DISTINCT p.EMPLOYEEID) = (
    -- 先找出所有项目中最大的员工数
    SELECT MAX(emp_count)
    FROM (
        -- 统计每个项目的员工数
        SELECT COUNT(DISTINCT EMPLOYEEID) AS emp_count
        FROM PROJECT24
        GROUP BY PROJECTID
    ) project_counts
);

逻辑和方法一类似，只是通过嵌套子查询先拿到最大员工数，再筛选出员工数等于这个最大值的项目，同样支持并列情况。

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