如何从酒店获得最低价的房间

问题描述

朋友们，

我有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

推荐答案

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 :)

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

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