php - 平均和比较左连接 MySQL

Question

我正在尝试从 2 个不同的 MySQL 表中获取信息。

主表是这个:

从这个中，我使用这些信息来获取平均费率:

如何编写一个 SQL 查询，通过计算 rating_house = house_id 的所有行来获得平均评级，并按最高评级排序(如果相等)评分，评分最高的评分。

这是我自己想出的:

$sql = "SELECT l.location_address, "
            . "r.rating_structure+r.rating_inventory+r.rating_service/3 AS average "
            . "FROM houses h "
            . "LEFT JOIN rating r ON h.house_id = r.rating_house "
            . "LEFT JOIN location l ON h.house_address = l.location_id "
            . "WHERE h.house_deleted IS NULL SORT BY average DESC LIMIT 10";
$result = $db_connect->prepare($sql);
if($result->execute()){
    while($user_data = $result->fetch(PDO::FETCH_ASSOC)){
        $user_data['location_address']."<br>";
    }
}

但是我没有得到任何输出？

Answer 1

为了提高性能，如果您知道相关行必须存在，请使用 INNER JOIN 而不是 LEFT JOIN。

SELECT l.location_address,
       (SUM(r.rating_structure)+SUM(r.rating_inventory)+SUM(r.rating_service))/3 AS average 
FROM houses h
INNER JOIN rating r ON h.house_id = r.rating_house
INNER JOIN location l ON h.house_address = l.location_id 
WHERE h.house_deleted IS NULL 
GROUP BY l.location_address
ORDER BY 2, 
         (SUM(r.rating_structure)+SUM(r.rating_inventory)+SUM(r.rating_service)) DESC
LIMIT 10