如何从酒店获得最低价的房间 [英] How to take min price room from a hotel

问题描述

朋友们，

我有3张桌子，酒店，房间，价格.我想要的是我想以最低的价格租下房间.我正在寻找城市.

酒店桌

HotelId名称TotalRooms地址城市
< t> 1 A 3 cc D
2 B 2 xx D

房间

RoomId HotelId RName
1 1 AC
2 1 BC
3 1 CC
4 2 DX
5 2 EX
价格

房间ID价格
1234
2 200
3150
4 500
5 210

因此，在与城市D一起搜索时
我想要结果为

hotelID RoomId RName价格
1 3 cc 150
2 5 EX 210


我的查询是

Hi friends,

I have 3 tables , Hotel, Room, Price. what I want is i want to take the room with minimum price. I''m searching with city.

Hotel table

HotelId Name TotalRooms Address City
<t> 1 A 3 cc D
2 B 2 xx D

Room

RoomId HotelId RName
1 1 AC
2 1 BC
3 1 CC
4 2 DX
5 2 EX
Price

RoomID Price
1 234
2 200
3 150
4 500
5 210

so while searching with city D
i want the result as

hotelID RoomId RName Price
1 3 cc 150
2 5 EX 210


my query is

select   min(price) as price ,Pr.RoomId,H.Hotelid,H.Name from hotel H
     join Rooms R on H.HotelId=R.hotelId
     join Prices PR on R.RoomId  = PR.RoomId
 where city='Dubai' and price>0
      group by  PR.RoomId,H.Hotelid,H.Name

它没有给出我想要的结果


任何帮助表示赞赏

问候

Chinnu

its not giving the result i want


Any help is appreciated

Regards

Chinnu

推荐答案

Chinnu ...

试试这个代码块

注意:-用现有的列和表名替换列名和表名.

Hi Chinnu ...

Try this code block

NOTE :- Replace column names and table name with your existing column and tables names.

SELECT HotelName,RoomName,Price FROM (
     SELECT H.Name As HotelName,H.TotalRooms,R.RoomName,RP.Price,
     ROW_NUMBER() OVER(PARTITION BY H.HotelId ORDER BY RP.Price) RowNum
             FROM Hotel H
          JOIN ROOMS R ON H.HotelId = R.HotelID
          JOIN PRICES RP ON RP.RoomId = R.RoomId
     WHERE CITY = 'D') AS A
WHERE A.RowNum = 1

谢谢

Thank you

试试这个

SELECT h.HotelID, h.Name, pr.RoomID, mn.MinPrice
  FROM Hotel h
  JOIN (SELECT r.HotelID, MIN(p.Price) AS MinPrice
          FROM Room AS r
          JOIN Price AS p
            ON p.RoomID = r.RoomID
         GROUP BY r.HotelID ) AS mn
    ON mn.HotelID = h.HotelID
  JOIN Room AS r
    ON mn.HotelID = r.HotelID AND
       mn.MinPrice = r.Price
 WHERE h.City = 'Dubai' AND pr.MinPrice > 0

祝您好运:)

Good luck :)

您的问题是您的GROUP BY.这将为您提供每个组的最低价格，而不是整体价格.由于您的团队是针对特定房间的，因此您将获得所有价格.您可以做几件事.我喜欢做的事情很简单，例如TOP 1解决方案:

Your problem is your GROUP BY. This will give you the minimum price for every group, not overall. Since your groups are room-specific, you are going to get every price. There are a couple things you can do. What I like to do is something simple, like a TOP 1 solution:

select TOP 1 PR.price ,Pr.RoomId,H.Hotelid,H.Name from hotel H
     join Rooms R on H.HotelId=R.hotelId
     join Prices PR on R.RoomId  = PR.RoomId
where city='Dubai' and price>0
ORDER BY PR.price

这将为您提供最便宜的房间.如果您想要三个最便宜的房间，可以将其更改为TOP3.

但是，这不会给您带来联系.相反，它只会给您带来第一个纽带.因此，如果您有三个房间，每个房间都需要花费

That will give you your least-expensive room. You could change it to TOP 3 if you wanted your three least expensive rooms.

However, this won''t give you the ties. Instead, it will just give you the first of the ties. So, if you have three rooms that each cost

这篇关于如何从酒店获得最低价的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！