mysql - 根据另外 2 张 table 返回可用房间

Question

这是我的表结构:

___房间

|--------|----------|
| ROO_Id | ROO_Name |
|--------|----------|
|      1 |     Blue |
|      2 |      Red |
|      3 |   Yellow |
|--------|----------|

___费率

|--------|---------------|-------------|-----------|-------------------|---------------|
| RAT_Id | RAT_DateStart | RAT_DateEnd | RAT_Price | RAT_RoomsAffected | RAT_RoomsList |
|--------|---------------|-------------|-----------|-------------------|---------------|
|     90 |    2019-04-01 |  2019-05-01 |    139.00 |               all |               |
|     91 |    2019-05-01 |  2019-12-31 |    159.00 |              list |           1,2 |
|     92 |    2019-05-01 |  2019-12-31 |    129.00 |              list |             3 |
|--------|---------------|-------------|-----------|-------------------|---------------|

___可用性

|--------|------------|------------|------------|
| AVA_Id | AVA_RoomId | AVA_Status | AVA_Date   |
|--------|------------|------------|------------|
|    203 |          1 |       Open | 2019-04-01 |
|    204 |          1 |       Open | 2019-04-02 |
|    205 |          1 |       Open | 2019-04-03 |
|    206 |          1 |       Open | 2019-04-04 |
|    207 |          1 |       Open | 2019-04-05 |
|    208 |          1 |      Close | 2019-04-06 |
|    209 |          1 |      Close | 2019-04-07 |
|--------|------------|------------|------------|

<小时/>

以下是这 3 个表的快速说明:

• _Rooms 包含有关我可以在酒店租用的房间的信息。
• ___Rates 包含我可以在特殊时期和客房申请的房价信息(如果 RAT_RoomsAffected 设置为 all，则意味着该房价可以应用于我酒店的所有客房。如果 RAT_RoomsAffected 设置为列表，则只能应用 RAT_RoomsList 字段中的房间 ID 的费率。
• ___Availities 列出每天预订此房间的可能性(开放)或无法预订房间(关闭)。

<小时/>

我的问题如下:

我想列出符合以下两个条件的房间:

• 对于客户想要在我的酒店预订的日期，该客房应处于开放状态 (AVA_Status)。例如，从2019年4月2日到2019年4月4日(2晚)。

• 此房间应与 _Rates 表中的价格关联。因此，根据客户的入住日期，我需要循环进入此表并找到该期间的费率。

• 最后，找到的费率应与第一步中找到的 ROO_Id 相匹配。如果 RAT_RoomsAffected = all 或在 RAT_RoomsList 列表中找到此 ROO_Id，则 ROO_Id 可能匹配。

<小时/>

所需的输出应该是从 2019-04-02 到 2019-04-04 的输出:

|--------|----------|-----------|
| ROO_Id | ROO_Name | RAT_Price |
|--------|----------|-----------|
|      1 |     Blue |    139.00 |
|--------|----------|-----------|

<小时/>

这是可以帮助您的 SQLFiddle: https://www.db-fiddle.com/f/kPY6FmKw1SZJjTHUbLj5Pe/0

非常感谢我收到的任何帮助。

Answer 1

我建议您在应用程序中编写此逻辑或重新设计数据库。您的表看起来应该是对象，并且现在一切都是这样，没有简单的方法或优雅的方法来获得您想要的结果，并且您绝对不能在单个查询中完成它。最引人注目的是 RAT_RoomsList 列，它是一个表示列表的字符串。此外，如果请求的预订日期范围与多个房价重叠，您会怎么做？

<小时/>

因为这是用 mysql 标记的

我能想到的最简单的解决方案是将您的“房价”表合并到“可用性”表中，以便每个房间和日期组合都有一个房价。费率表将变得多余。

然后就可以查询:

SELECT AVA_RoomId, SUM(RAT_price)
FROM   ___Availabilities
WHERE  AVA_Status = 'Open' AND
       AVA_Date >= '2019-04-02' AND
       AVA_Date < '2019-04-04'
GROUP BY AVA_RoomId
HAVING COUNT(AVA_RoomId) = DATEDIFF('2019-04-04', '2019-04-02');

住宿总价，或:

SELECT AVA_RoomId, AVA_Date, RAT_price
FROM   ___Availabilities
WHERE  AVA_Status = 'Open' AND
       AVA_Date >= '2019-04-02' AND
       AVA_Date < '2019-04-04';

仅获取在此期间开放的房间及其价格的列表，您可以在应用程序中对其进行排序。

db-fiddle