MySQL多HAVING条件查询报错，求正确写法指导

你的查询出错主要有两个核心问题：

• 重复使用HAVING关键字，MySQL不允许单条查询里多次声明HAVING子句，多个过滤条件需要合并到同一个HAVING中，用AND/OR逻辑运算符连接。
• GROUP BY子句位置错误，必须放在HAVING之前，这是MySQL语法的固定顺序要求。

修正后的查询语句如下：

SELECT    schools.*,
          schools.required_gpa AS gpa,
          (college_act_scores.min_act + college_act_scores.max_act)/2 AS act_avrg ,
          (college_sat_scores.min_sat + college_sat_scores.max_sat)/2 AS sat_avrg,
          (6371 * Acos( Cos( Radians(31.4699398) ) * Cos( Radians( latitude ) ) * Cos( Radians( longtitude ) - Radians(74.3096108) ) + Sin( Radians(31.4699398) ) * Sin( Radians( latitude ) ) ) ) AS distance
FROM      schools
LEFT JOIN college_paying1
ON        college_paying1.college_id = schools.id
LEFT JOIN college_act_scores
ON        (
                    college_act_scores.college_id = schools.id
          AND       college_act_scores.college_child_sub_cat_id = 144)
LEFT JOIN college_sat_scores
ON        (
                    college_sat_scores.college_id = schools.id
          AND       college_sat_scores.college_child_sub_cat_id = 136)
WHERE     college_paying1.on_campus >= 0
AND       college_paying1.on_campus <=80348
AND       college_paying1.college_child_sub_cat_id =120
GROUP BY  schools.id
HAVING    act_avrg BETWEEN 0 AND 36
AND       sat_avrg BETWEEN 0 AND 1600
ORDER BY  distance ASC limit 0, 10

关键修改说明： #

1. 合并HAVING条件：将两个独立的HAVING子句合并为一个，用AND连接两个范围判断，实现同时过滤act_avrg和sat_avrg的需求。
2. 调整子句顺序：把GROUP BY schools.id移到HAVING之前，遵循MySQL标准语法顺序：FROM→JOIN→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT。

额外补充：因为你用了LEFT JOIN，act_avrg或sat_avrg可能出现NULL值，如果需要保留这些无分数的学校记录，可以在HAVING里加入空值判断，示例如下：

HAVING    (act_avrg BETWEEN 0 AND 36 OR act_avrg IS NULL)
AND       (sat_avrg BETWEEN 0 AND 1600 OR sat_avrg IS NULL)

是否添加该判断，取决于你对无分数学校的筛选需求。

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