房间搜索SQL查询

Question

这是一个酒店代理的示例程序，他从各个酒店获取合同并允许客户搜索和预订与之签订合同的酒店（从而获得一些利润）。以下是我的表模式。

CONTRACT (contract_id, hotel_id, valid_from, valid_to) 
ROOM (room_type, hotel_id, contract_id, price, max_adults_allowed, avail_rooms)
HOTEL (hotel_id, hotel_name, location)

对于搜索，界面可以像hotels.com一样; 例如，客户可以指定“1个房间2名成人”和“1个房间2名成人和1个房间3名成人”。

以下是我的SQL查询：

select h.name, h.location, r.type, r.price FROM HOTEL h, ROOM r, CONTRACT c 
WHERE c.contract_id = r.contract_id and c.hotel_id = h.hotel_id    --table joining
AND 'requested room lies within the contract valid period'  --check in and check out dates comparison
AND 'h.location is the user specified location' --requested location 
........

我在将房间搜索详情（如上所述）整合到这个查询中遇到了问题。有人能帮我编写包含房间规格的SQL查询吗？我正在使用SQL Server。提前感谢。

- samsamara

---

这很棘手，因为询问“1间房2名成人和1间房3名成人”可能意味着您想要一个满足两个房间需求的单一酒店。 - Gabe

不一定是单个酒店，如果有多个酒店可用，则可以是任意数量的酒店。 - samsamara

你的意思是问“1间房2名成人和1间房3名成人”可能意味着一家酒店的一间房间供应给2名成人，而另一家酒店的一间房间供应给3名成人？ - Gabe

哦不好意思。它们应该在同一家酒店。即“1间房2名成人和1间房3名成人”应属于同一家酒店。 - samsamara

Answer 1

此脚本检索仅拥有所需客房的酒店。如果酒店仅有一间所需客房，则该酒店将被排除。

更新于2013年1月15日

IF OBJECT_ID('tempdb.dbo.#RoomParams') IS NOT NULL DROP TABLE dbo.#RoomParams
SELECT SUM(rooms) AS rooms, adults
INTO dbo.#RoomParams
FROM (VALUES(1, 2), -- 1 room with 2 adults
            (1, 2), -- 1 room with 2 adults
            (1, 3)) -- 1 room with 3 adults
p(rooms, adults)
GROUP BY adults

;WITH cte AS
 (
  SELECT h.hotel_id, h.hotel_name, h.location, r.room_type, r.price,
         SUM(avail_rooms) OVER (PARTITION BY h.hotel_id, r.max_adults_allowed) AS cnt,
         r.max_adults_allowed        
  FROM CONTRACT c JOIN ROOM r ON c.contract_id = r.contract_id AND c.hotel_id = r.hotel_id
                  JOIN HOTEL h ON c.hotel_id = h.hotel_id
  WHERE c.valid_from >= '20130114' AND c.valid_to <= '20130115'  --check in and check out dates comparison
        AND h.location IN ('loc4') --requested location        
  )
  SELECT *
  FROM cte s
  WHERE 
    NOT EXISTS (                  
                SELECT rp.adults
                FROM dbo.#RoomParams rp
                EXCEPT
                SELECT st.max_adults_allowed
                FROM cte st JOIN dbo.#RoomParams r ON st.cnt >= r.rooms AND st.max_adults_allowed = r.adults
                WHERE st.hotel_id = s.hotel_id                  
                ) AND s.max_adults_allowed IN (SELECT adults FROM dbo.#RoomParams)

演示 SQLFiddle。

如果您想检索至少有一个选项的所有酒店

IF OBJECT_ID('tempdb.dbo.#RoomParams') IS NOT NULL DROP TABLE dbo.#RoomParams
SELECT rooms, adults
INTO dbo.#RoomParams
FROM (VALUES(1, 2), -- 1 room with 2 adults
            (1, 2), -- 1 room with 2 adults
            (1, 3)) -- 1 room with 3 adults
p(rooms, adults)

;WITH cte AS
 (
  SELECT h.hotel_id, h.hotel_name, h.location, r.room_type, r.price,
         SUM(avail_rooms) OVER (PARTITION BY h.hotel_id, r.max_adults_allowed) AS cnt,
         r.max_adults_allowed
  FROM CONTRACT c JOIN ROOM r ON c.contract_id = r.contract_id AND c.hotel_id = r.hotel_id
                  JOIN HOTEL h ON c.hotel_id = h.hotel_id
  WHERE c.valid_from >= '20130114' AND c.valid_to <= '20130115'  --check in and check out dates comparison
        AND h.location IN ('loc4') --requested location
  )
  SELECT hotel_name, location, room_type, price, max_adults_allowed, cnt
  FROM cte c 
  WHERE EXISTS (
                SELECT 1
                FROM dbo.#RoomParams r 
                WHERE c.cnt >= r.rooms AND c.max_adults_allowed = r.adults
                )

- Aleksandr Fedorenko

Answer 2

假设您已经解析了“自然语言”输入，请按max_adults分组您的请求，然后查询每个单独的类型；例如，对于“2间房1名成年人和3间房2名成年人”，请发出两个不同的查询，其中一个是（max_adults = 1，avail_rooms = 2），另一个是（max_adults = 2，avail_rooms = 3）。

select h.name, h.location, r.type, r.price, r.room_id FROM HOTEL h, ROOM r, CONTRACT c 
WHERE c.contract_id = r.contract_id and c.hotel_id = h.hotel_id
AND c.valid_from >= ? AND c.valid_to <= ? AND h.location = ?
AND r.max_adults = ? AND r.avail_rooms >= ?

请注意，这只会找到max_adults的精确匹配项，并不会给您“低于预订”房间的选项。如果您想允许客户预订大型房间（并相应支付费用，而不必填满它们），可以使用以下策略：首先尝试原始查询，然后搜索更大的房间（通过添加“虚拟客人”）。

例如，如果原始请求是“1个三人间，1个双人间，1个单人间”，则首先尝试该请求，然后尝试“1个三人间2个双人间”，然后是“2个三人间1个双人间”，最后是“3个三人间”。对于每个“修改后”的查询，请使用上面的SQL。第一个成功的查询将是客户最便宜的选择。

即使这涉及多个SQL事务，但每个事务都有简单的连接，可能比复杂的语句更快地工作。

如果您有更多的控制权，您可能会更改架构以针对每个房间进行账户管理：

ROOM (room_id, room_type, hotel_id, contract_id, price, max_adults_allowed)
BOOKING (room_id, checkin, checkout)

- dan3

---

但是这里的问题在于假设我只有一个房间，可以容纳3个成年人。现在如果我搜索“1个房间2个成年人和1个房间3个成年人”，并且发送两个查询，我会得到一个结果，但它是无效的，因为我只有一个房间可以容纳3个成年人。 - samsamara

我在我的答案中明确表示，我的策略不涉及欠订房间，即允许3人房间中有2名成年人（查询后的段落）。您需要定义欠订时会发生什么。客户是否同意支付未入住的客人费用（以便酒店不会因为大房间而亏损）？在这种情况下，请告诉客户您找不到所请求的组合，并询问他们是否愿意支付更多费用；如果愿意，请将初始自然语言查询更改为寻找更昂贵的房间并重新运行SQL。 - dan3

或者不要问任何问题，只需尝试原始查询，然后尝试添加更多的“人工”客人。因此，如果原始查询是“1个三人间，1个双人间，1个单人间”，请先尝试这个，然后尝试“1个三人间2个双人间”，然后尝试“2个三人间1个双人间”，然后是“3个三人间”。第一个成功的将是最便宜的选择。即使这涉及几个SQL事务，每个事务都有简单的连接，并且可能比复杂的语句更快。我会更新我的答案。 - dan3