使用COUNT聚合函数获取员工关联课程最值时存储过程报错求助

你触发的错误根源在于：

• 子查询直接从course_test表调用MAX(total_emp)，但total_emp是聚合后的别名，原表根本不存在这个字段
• HAVING子句里的子查询没有先计算每门课程对应的员工数，直接对原表使用聚合函数，导致出现“聚合函数嵌套”的非法操作

推荐用CTE（公共表表达式）简化逻辑，先算出所有课程的员工数量，再筛选出最值对应的课程：

CREATE PROCEDURE sp_max_min_EmployeeCourses4
AS
WITH CourseEmployeeCounts AS (
    SELECT 
        c.course_name,
        COUNT(ec.emp_id) AS total_emp
    FROM [dbo].[course_test] c
    LEFT JOIN employee_courses ec ON c.course_id = ec.course_id
    GROUP BY c.course_name
)
SELECT 
    course_name,
    total_emp
FROM CourseEmployeeCounts
WHERE total_emp = (SELECT MAX(total_emp) FROM CourseEmployeeCounts)
   OR total_emp = (SELECT MIN(total_emp) FROM CourseEmployeeCounts);

如果偏好嵌套子查询的写法，也可以这样实现：

CREATE PROCEDURE sp_max_min_EmployeeCourses4
AS
SELECT 
    course_name,
    total_emp
FROM (
    SELECT 
        c.course_name,
        COUNT(ec.emp_id) AS total_emp
    FROM [dbo].[course_test] c
    LEFT JOIN employee_courses ec ON c.course_id = ec.course_id
    GROUP BY c.course_name
) AS CourseCounts
WHERE total_emp = (SELECT MAX(total_emp) FROM (
    SELECT COUNT(ec.emp_id) AS total_emp
    FROM [dbo].[course_test] c
    LEFT JOIN employee_courses ec ON c.course_id = ec.course_id
    GROUP BY c.course_name
) AS MaxMinCounts)
OR total_emp = (SELECT MIN(total_emp) FROM (
    SELECT COUNT(ec.emp_id) AS total_emp
    FROM [dbo].[course_test] c
    LEFT JOIN employee_courses ec ON c.course_id = ec.course_id
    GROUP BY c.course_name
) AS MaxMinCounts);

1. 通过LEFT JOIN和COUNT(ec.emp_id)计算每门课程关联的员工数（无员工关联的课程会返回0）
2. 基于计算结果，分别提取员工数的最大值和最小值
3. 最终筛选出员工数等于最大值或最小值的课程记录

内容的提问来源于stack exchange，提问作者Reham.Dev