我正试图统计每个城市的酒店数量。
我有两张同样的桌子。第一张桌子是hotel_master
hotel_master
id | hotel_name | city_code
第二张表是city_master
city_master
id | city_code | city_name
可以从查询中获得计数吗?
首先,我已经尝试过这个查询,或者我需要使用do-while循环?
SELECT
hotel_master.hotel_name,
city_master.city_name
FROM hotel_master
INNER JOIN city_master ON hotel_master.city_code = city_master.city_code
请帮我做同样的事。
SELECT COUNT(h.city_code) as total_hotel, c.city_name
FROM hotel_master h
INNER JOIN city_master c ON h.city_code = c.city_code
GROUP BY h.city_code
在hotel_master
的字段上使用COUNT
。它将获得基于城市的酒店数量。
需要在查询中使用GROUP BY
子句来获取每个城市的酒店数量。
SELECT city_master.city_name, count(hotel_master.id) hotetcount
FROM hotel_master
JOIN city_master
ON hotel_master.city_code = city_master.city_code
GROUP
BY city_master.city_code
你可以试试这个:
SELECT COUNT(*)
FROM hotel_master
JOIN city_master
ON hotel_master.city_code = city_master.city_code
GROUP
BY hotel_master.city_code
只需使用COUNT()
和GROUP BY
即可完成
SELECT
count(hotel_master.hotel_name),
city_master.city_name
FROM hotel_master
INNER JOIN city_master ON hotel_master.city_code = city_master.city_code
GROUP BY city_master.city_name