mysql - 查找查询以显示评级大于给定值的名称时遇到问题

Question

table 公寓>

apartment_id || apartment_name || status
==============================
 1                         joys                active
 5                         harrys              active  
 10                        tols                active 

表格评分>

user_id || apartment_id || rating
===================================
  1              1                  4
  2              5                  3
  3              10                 4
  4              10                 5  

我需要获取评分大于某个值(例如“x”)的公寓名称。如果同一公寓有超过 1 个来自不同用户的评分(如 apartment_id = 10)，则应归还公寓，如果平均值大于“x”..

SELECT a.*  FROM  `apartment` AS a     LEFT JOIN `ratings` AS b ON a.`apartment_id` = b.`apartment_id` WHERE  a.`status` NOT IN ('inactive')  AND (b.apartment_id IN (SELECT apartment_id FROM `ratings` WHERE `rating` > x)) GROUP BY a.`apartment_id` 

如果公寓只有一个评级，上面的查询就可以正常工作..如果公寓有多个评级值，请帮忙

Answer 1

SELECT a.apartment_name,
       t.avg_rating
FROM apartment a
LEFT JOIN(SELECT s.apartment_id , AVG(s.rating) as avg_rating
     FROM ratings s
     GROUP BY s.apartment_id
     HAVING AVG(s.rating) > <X..> ) t
 ON(t.apartment_id = a.apartment_id)
WHERE a.status NOT IN ('inactive')