SQL查询报错：HAVING子句中未知字段Enrollment.final_grade，求解决

问题背景 #

执行SQL查询时触发错误：Error: ER_BAD_FIELD_ERROR: Unknown column 'Enrollment.final_grade' in 'having clause'，需求为统计每位教授所授同一课程中，退课截止前退课（成绩为'W'）的学生人数。

数据库表结构 #

CREATE TABLE Student (
  student_id INT(9) PRIMARY KEY, 
  name VARCHAR(100) NOT NULL, 
  gpa DECIMAL(3,2) DEFAULT 0.00
);

CREATE TABLE Course (
  course_id CHAR(8) PRIMARY KEY, 
  description VARCHAR(100) NOT NULL, 
  units INT DEFAULT 3
);

CREATE TABLE CourseSection (
  course CHAR(8) NOT NULL,
  section INT(1) DEFAULT 1,
  CONSTRAINT FK_SECTION_COURSE FOREIGN KEY (course) REFERENCES Course(course_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE CoursePrerequisites ( 
  course CHAR(8) NOT NULL,
  prerequisite CHAR(8) NOT NULL, 
  CONSTRAINT FK_COURSEPREREQUISITES_COURSE FOREIGN KEY (course) REFERENCES Course(course_id) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT FK_COURSEPREREQUISITES_PREREQUISITES FOREIGN KEY (prerequisite) REFERENCES Course(course_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Faculty (
  faculty_id INT(9) PRIMARY KEY, 
  name VARCHAR(100) NOT NULL, 
  role ENUM ('professor','researcher','both')
);

CREATE TABLE Application (
  application_id INT PRIMARY KEY AUTO_INCREMENT, 
  student INT(9) NOT NULL, 
  program ENUM('Phd', 'Master', 'Undergrad'),
  department ENUM("CS", "MATH"),
  status ENUM("Admitted", "Rejected") DEFAULT "Rejected",
  CONSTRAINT FK_APPLICATION_STUDENT FOREIGN KEY (student) REFERENCES Student(student_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Enrollment (
  enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
  course CHAR(8) NOT NULL,
  student INT(9) NOT NULL,
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  semester ENUM('FA', 'SP', 'SU'), 
  status ENUM('dropped', 'enrolled') DEFAULT "enrolled",
  final_grade ENUM('A', 'B', 'C', 'D', 'F', 'NC', 'IC', 'CR', 'W', 'WU', 'NA') DEFAULT 'A',
  CONSTRAINT FK_ENROLLMENT_COURSE FOREIGN KEY (course) REFERENCES Course(course_id) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT FK_ENROLLMENT_STUDENT FOREIGN KEY (student) REFERENCES Student(student_id) ON DELETE CASCADE ON UPDATE CASCADE 
);

错误的查询语句 #

SELECT Course.course_id, Student.student_id, COUNT(Enrollment.final_grade) 
FROM Enrollment
JOIN Course ON Course.course_id = Enrollment.course
JOIN Student ON Student.student_id = Enrollment.student
GROUP BY Course.course_id
HAVING Enrollment.final_grade = 'W';

核心问题分析 #

1. HAVING子句误用：HAVING仅用于过滤分组后的聚合结果，不能直接引用未被聚合或未包含在GROUP BY中的原始表字段。要筛选成绩为'W'的退课记录，必须用WHERE子句在分组前完成过滤。
2. 缺失教授与课程的关联：需求要求统计教授的授课数据，但当前表结构中没有建立教授与课程/课程段的绑定关系（CourseSection表缺少faculty_id字段），原SQL也未关联Faculty表，根本无法关联教授和课程。
3. GROUP BY与SELECT字段不匹配：原SQLSELECT了Student.student_id，但GROUP BY仅按Course.course_id分组，违反SQL分组规则（非聚合字段必须出现在GROUP BY中），且需求是统计人数，无需单独列出学生ID。
4. 统计逻辑冗余：COUNT(Enrollment.final_grade)不如直接用COUNT(*)或COUNT(student_id)高效，因为已经筛选了final_grade='W'的记录。

修正方案 #

第一步：补充表结构的关联关系 #

首先需要建立教授与课程段的关联，同时调整Enrollment表关联到课程段（同一课程可能有多个教授授课的section）：

-- 给CourseSection添加授课教授字段及外键
ALTER TABLE CourseSection
ADD COLUMN faculty_id INT(9) NOT NULL,
ADD CONSTRAINT FK_COURSESECTION_FACULTY FOREIGN KEY (faculty_id) REFERENCES Faculty(faculty_id) ON DELETE CASCADE ON UPDATE CASCADE;

-- 修改Enrollment表，使其关联CourseSection（课程+section唯一标识一个授课单元）
ALTER TABLE Enrollment
DROP FOREIGN KEY FK_ENROLLMENT_COURSE,
ADD COLUMN section INT(1) DEFAULT 1,
ADD CONSTRAINT FK_ENROLLMENT_COURSESECTION FOREIGN KEY (course, section) REFERENCES CourseSection(course, section) ON DELETE CASCADE ON UPDATE CASCADE;

第二步：正确的查询语句 #

SELECT 
  f.faculty_id,
  f.name AS professor_name,
  c.course_id,
  c.description AS course_name,
  COUNT(e.student_id) AS dropped_student_count
FROM Enrollment e
JOIN CourseSection cs ON e.course = cs.course AND e.section = cs.section
JOIN Faculty f ON cs.faculty_id = f.faculty_id
JOIN Course c ON cs.course = c.course_id
WHERE e.final_grade = 'W' -- 分组前筛选退课记录
GROUP BY f.faculty_id, f.name, c.course_id, c.description -- 按教授+课程分组
ORDER BY f.faculty_id, c.course_id;

修正说明 #

• 用WHERE子句提前筛选退课记录，避免HAVING的错误使用。
• 通过CourseSection关联Faculty表，实现教授与课程的绑定，满足需求中的“每位教授所授课程”要求。
• GROUP BY包含所有非聚合字段，符合SQL标准，避免分组逻辑错误。
• 统计字段明确统计退课学生人数，同时返回教授和课程的详细信息，结果更具可读性。

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