MySQL 加入查询 GROUP BY

Question

我有 2 个表。 (酒店、地点)。

我想检索所有位置以及该位置的酒店数量。

现在我有以下查询:

SELECT locations.*, 
COUNT(*) as no_of_hotels 
FROM locations RIGHT JOIN hotels ON hotels.location_id = locations.id 
GROUP BY locations.id 
ORDER BY no_of_hotels DESC

查询工作正常，但问题是，它只给我包含 1 个或多个酒店的位置。我想显示所有位置(即使该位置没有酒店)。

Answer 1

你应该改为左连接:

SELECT locations.*, 
       COUNT(distinct hotels.id) as no_of_hotels 
       FROM locations 
       LEFT JOIN hotels ON hotels.location_id = locations.id 
       GROUP BY locations.id 
       ORDER BY no_of_hotels DESC

否则这可能更容易理解:

select
  locations.*
  (select count(*) from hotels where hotels.location_id = locations.id) as no_of_hotels 
from
  locations
order by no_of_hotels desc