mysql - 根据其他 2 张桌子返回可用房间

问题描述

这是我的表结构：

___房间

|--------|----------|
| 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 设置为 list，则房价可能是仅应用 RAT_RoomsList 字段中的房间 ID）。
___Availabilities每天列出预订此房间的可能性（打开）或不可能预订房间（关闭）。

我的问题如下：

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

此房间应具有Open客户想要在我的酒店预订的日期的状态 (AVA_Status)。例如，从 2019-04-02 到 2019-04-04（2 晚）。
这间客房应与_Rates表中的房价挂钩。因此，根据客户的逗留日期，我需要循环进入此表并找到此期间的费率。
最后找到的费率应该与ROO_Id第一步中找到的相匹配。如果或何时在列表中找到它，则AROO_Id可能匹配。RAT_RoomsAffected = allROO_IdRAT_RoomsList

所需的输出应该是从 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

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

标签： mysql

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

由于这是用 mysql 标记的

我能想到的最简单的解决方案是将您的 Rates 表合并到您的 Availabilities 表中，这样对于每个房间和日期的组合，也有一个价格。Rates 表将变得多余。

然后可以查询：

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小提琴

mysql - 根据其他 2 张桌子返回可用房间

问题描述

解决方案

由于这是用 mysql 标记的

推荐阅读