将多个房间可用性查询合并为一个查询

Question

我目前正在尝试通过合并查询来优化数据库。但是在优化房间可用性查询时，我一直遇到瓶颈。

我有一个房间可用性表，每个记录都表示每个日期可用的房间数量。它的格式如下：

• room_availability_id (PK)
• room_availability_rid (fk_room_id)
• room_availability_date (2011-02-11)
• room_availability_number (可用房间数量)

问题在于获取每个提供日期可用的房间列表。当我使用IN()时：

WHERE room_availability_date IN('2011-02-13','2011-02-14','2011-02-15')
AND room_availability_number > 0

如果第14个日期的可用性为0，它仍然会给我其他两个日期。但是我只想在所有三个日期都可用时才获得那个room_id。

请告诉我是否有一种方法可以在MySQL中做到这一点，而不是分别查询每个日期/房间/可用性组合（这就是现在所做的 :-( ）。

我尝试了各种组合，尝试使用room_availability_date = ALL (...)，尝试了一些肮脏的重复子查询，但都没有成功。

提前感谢您的任何想法！

- DaFrenk

Answer 1

您需要构建一个查询以按房间ID分组，然后检查每个日期的可用性，这可以使用having子句完成。保留room_availability_date的where子句谓词将有助于保持查询的效率（因为索引等不容易与having子句一起使用）。

SELECT 
    room_availability_rid

WHERE room_availability_date IN ('2011-02-13','2011-02-14','2011-02-15')
  AND room_availability_number > 0

GROUP BY room_availability_rid 

HAVING count(case room_availability_date when '2011-02-13' THEN 1 END) > 0
   AND count(case room_availability_date when '2011-02-14' THEN 1 END) > 0
   AND count(case room_availability_date when '2011-02-15' THEN 1 END) > 0

- a'r

---

通过简单地计算进入每个组的行数来简化此过程。（可以安全地假设每个房间每天只有一条记录吗？） - awm

谢谢A'r！我已经进行了快速测试，它似乎完美地工作了！我需要进行一些彻底的测试，以查看它是否可以在完整查询的更多位置中使用。@awm：是的，每个房间/日期/可用性组合应该只有1条记录。优化得越多越好！ - DaFrenk

Answer 2

我认为我可以在a'r的答案上进行改进：

SELECT 
    room_availability_rid, count(*) n

WHERE room_availability_date IN ('2011-02-13','2011-02-14','2011-02-15')
  AND room_availability_number > 0

GROUP BY room_availability_rid 

HAVING n=3

编辑：这当然是基于每个房间每天只有一个表格条目的假设。这个假设是否成立？

- awm

---

谢谢Awm，这个方法似乎也完美地运行了！因为是的，应该只有1个房间/日期/数量组合。 - DaFrenk

Answer 3

您可以按照房间 ID 进行分组，生成一个可用日期列表，然后查看是否包含您所需的所有日期。

这将为您提供每个房间可用的日期列表：

select `room_availability_rid`,group_concat(`room_ availability_date`) as `datelist`
       from `table` where room_availability_number>0 
       group by `room_availability_rid`

然后我们可以添加一个having子句来获取所有需要日期上可用的房间：

select `room_availability_rid`,group_concat(`room_ availability_date`) as `datelist`
        from `table` where room_availability_number>0
        group by `room_availability_rid`
        having find_in_set('2011-02-13',`datelist`) and
               find_in_set('2011-02-14',`datelist`) and
               find_in_set('2011-02-15',`datelist`)

这应该可以工作。你能帮我测试一下吗？:)

- awm