phpmyadmin Mysql查询有多个条件创建错误

nom7f22z 于 2022-11-09 发布在 PHP

关注(0)|答案(2)|浏览(123)

我正在尝试多个拥有，但问题是多个拥有条件，任何一个指导我如何调整同一查询中的两个拥有

以下是SQL查询：

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
HAVING    (
                    act_avrg BETWEEN 0 AND       36)
having    (
                    sat_avrg BETWEEN 0 AND       1600)
GROUP BY  schools.id
ORDER BY  distance ASC limit 0, 10

phpmyadmin

来源：https://stackoverflow.com/questions/73636140/mysql-query-with-multiple-having-conditions-creating-error

2条答案

按热度按时间

rryofs0p1#

您需要将HAVING子句移到GROUP BY之后。文档here
这应该可行：

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

希望这对你有帮助！干杯！

赞(0）回复(0）举报 2022-11-09

pinkon5k2#

而不是

HAVING ( act_avrg BETWEEN  0 AND 36)  HAVING ( sat_avrg BETWEEN  0 AND 1600)

卖出

HAVING (act_avrg BETWEEN  0 AND 36) AND (sat_avrg BETWEEN  0 AND 1600)

赞(0）回复(0）举报 2022-11-09

我来回答

phpmyadmin Mysql查询有多个条件创建错误

2条答案

相关问题

热门标签

最新问答