PHPMyAdmin中SQL查询HAVING子句报错：未知列'department'

尝试统计surveyresult表中，department1部门下各ciricullum评价的数量，但执行SQL时提示department列不存在。

错误SQL语句：

select count(ciricullum) as 'nooooo_rat',ciricullum 
from surveyresult 
group by ciricullum 
HAVING department ='department1';

报错信息：

select count(ciricullum) as 'nooooo_rat',ciricullum from surveyresult group by ciricullum HAVING department ='department1' LIMIT 0, 25

#1054 - Unknown column 'department' in 'having clause'

已尝试将department改为'department'，但错误依旧。

表结构DDL：

CREATE TABLE surveyresult (
  cust_name varchar(50) NOT NULL,
  department enum('department1','department2','department3','department4','department5') NOT NULL,
  facilities enum('good','neutral','bad','') NOT NULL,
  faculty enum('good','neutral','bad','') NOT NULL,
  extraciricular enum('good','neutral','bad','') NOT NULL,
  safety enum('good','neutral','bad','') NOT NULL,
  ciricullum enum('good','neutral','bad','') NOT NULL,
  comment varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

问题原因 #

1. HAVING子句适用场景错误：筛选特定部门的记录属于分组前的行级过滤，应该用WHERE而非HAVING。HAVING仅用于过滤分组后的聚合结果，只能引用SELECT列表、GROUP BY子句中的列或聚合函数。
2. 列不在分组/查询范围内：你的GROUP BY仅指定了ciricullum，department既不在GROUP BY中，也未出现在SELECT列表里，因此HAVING无法识别该列。

正确SQL写法 #

推荐写法（用WHERE先过滤再分组）

select count(ciricullum) as 'nooooo_rat', ciricullum 
from surveyresult 
WHERE department = 'department1'
group by ciricullum;

若一定要用HAVING（冗余，不推荐）

需将department加入GROUP BY和SELECT列表：

select count(ciricullum) as 'nooooo_rat', ciricullum, department
from surveyresult 
group by ciricullum, department
HAVING department = 'department1';

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