试图获得在查询中获利最多的酒店的房间 [英] trying to get the rooms of hotels that got the most profit in a query
问题描述
我正在尝试执行一个查询，该查询为我提供了每家酒店(25家酒店)中最赚钱的客房的总和
I'm trying to implement a query which give me the sum of most profitable room in each hotel(25 hotels)
以下是我的查询:
SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,sum(rooms.room_price) AS profit,COUNT(rooms.room_id) AS count
FROM hotels,rooms,bookings
WHERE hotels.hotel_id=rooms.hotel_id
AND rooms.room_id=bookings.room_id
GROUP BY rooms.room_id
SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,sum(rooms.room_price) AS profit,COUNT(rooms.room_id) AS count
FROM hotels,rooms,bookings
WHERE hotels.hotel_id=rooms.hotel_id
AND rooms.room_id=bookings.room_id
GROUP BY rooms.room_id
这是我得到的最接近的结果..忽略酒店名称语言
and this is the closest outcome i got.. ignore the hotel name language
hotel_id 1有5个房间，其中room_number 300利润最大.我只想显示每家酒店的最大利润.我不需要其他利润较少的房间.
hotel_id 1 has 5 rooms, the room_number 300 made the most profit. I want to show the most profit only of each hotel. I don't need the other rooms that made less profit.
更新: 因此，我解决了一个类似的查询，在该查询中，我想获得获利最多的最好的2个房间.但是，我只是想不出任何能给我每家酒店带来最大利益的功能.很少的提示或帮助就会被应用
Update: So i solved a similar query where i want to get the best 2 rooms that made the most profit. But, i just can't think of any function that can give me only best profit of each hotel. little hint or help would be appriciated
推荐答案
尝试以下查询:
SELECT * FROM
(SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,SUM(rooms.room_price) AS profit,COUNT(rooms.room_id) AS COUNT
FROM hotels,rooms,bookings
WHERE hotels.hotel_id=rooms.hotel_id
AND rooms.room_id=bookings.room_id
GROUP BY rooms.room_id) a GROUP BY hotel_id;
这可能会做到:
SELECT hotel_id,room_id,room_number,MAX(a.tc) AS "Count",MAX(tp) AS "MostProfit" FROM
(SELECT hotel_id,rooms.room_id,room_number,COUNT(rooms.room_id) AS "tc",SUM(room_price) AS "tp" FROM rooms JOIN bookings
ON rooms.room_id=bookings.room_id
GROUP BY rooms.room_id) a GROUP BY hotel_id
这篇关于试图获得在查询中获利最多的酒店的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
