MySQL CTE搭配GROUP BY HAVING报语法错误的解决方法

• 标量子查询使用语法错误：当在HAVING子句中用子查询返回单个值做等值判断时，子查询必须用圆括号包裹，原代码等号右侧的SELECT mxcnt FROM cte没有加括号，直接书写会触发语法解析错误。
• CTE字段引用逻辑错误：CTE是独立的临时结果集，不是全局可直接调用的变量。如果主查询没有通过JOIN关联CTE、也没有通过子查询的方式读取CTE内容，直接写cte.mxcnt会被数据库判定为不存在的字段，导致运行失败。

针对原有的CTE逻辑，只需要给右侧的标量子查询补充括号即可，在给出的测试数据下可以正确返回project_id=1的结果：

WITH cte AS (
    SELECT COUNT(employee_id) AS mxcnt 
    FROM Project 
    GROUP BY project_id 
    ORDER BY mxcnt DESC 
    LIMIT 1 
)
SELECT project_id 
FROM Project 
GROUP BY project_id 
HAVING COUNT(employee_id) = (SELECT mxcnt FROM cte);

上述写法用LIMIT 1取最大员工数，如果出现多个项目员工数并列第一的场景，会漏返回结果。可以用窗口函数RANK()实现，天然兼容并列第一的查询需求，不需要单独取最大值做比对：

WITH project_stat AS (
    SELECT
        project_id,
        COUNT(employee_id) AS emp_count,
        RANK() OVER(ORDER BY COUNT(employee_id) DESC) AS count_rank
    FROM Project
    GROUP BY project_id
)
SELECT project_id
FROM project_stat
WHERE count_rank = 1;

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