使用MySQL查找具有关系的多个日历中的可用性
我拥有一些我经常出租的东西。它由几个部分组成。它可以一部分一部分租，也可以作为一个整体租。如果一个部分租出去，你就不能把它作为一个整体租出去。
例如，我租了一辆车。这辆车的轮胎也是出租的。你可以选择租带轮胎的车（«整个»），或者只租一个轮胎。但是，如果有一个或多个轮胎出租，您将无法租用汽车（«整个»）。
我把它想象成一个层次结构。
Whole
_______|_______
| |
Part 1 Part 2
我使用了一个谷歌日历来描述“整体”，并为每个包含部分使用了单独的日历。这是可行的，但很累人，我想只发送一个链接给那些感兴趣的人，他们可以在那里看到什么是可用的。
所以我制作了一个简单的数据库（mariadb 10.4），有两个表：
# tbl: part
| id | parent_id | name |
列parent\u id只是引用同一表中的另一行，下面是一个数据示例。
| 1 | NULL | Car |
| 2 | 1 | Tire 1 |
| 3 | 1 | Tire 2 |
然后下表存储每个部分被预订时的日期（示例数据）。
# tbl: booking
| id | part_id | booked_from | booked_until |
--------------------------------------------
| 1 | 1 | 2021-07-31 | 2021-08-03 |
| 2 | 2 | 2021-08-03 | 2021-08-07 |
| 3 | 3 | 2021-08-04 | 2021-08-06 |
| 4 | 3 | 2021-08-09 | 2021-08-10 |
由此我们知道，汽车本身是从2021 07月31日预订的，但它只能从2021 08月06日预订，因为这段时间有两个轮胎出租（但它们可以同时出租，因为它们没有严格的关系）。但直到2021 08月09日，因为轮胎又被预订了。
我要找的是一个查询，当有可用的东西时，可以得到一个日期列表。从PAR表中，我能够找出哪些部分是相关的，这不是我最大的问题，我想，因为在查询可用性时可以使用类似的方法：
- 汽车：（1,2,3）中的零件id
- 轮胎1：（1,2）中的零件id
- 轮胎3：（1,3）中的零件id
我的问题是（简单地说？）我如何编排一个只返回可用日期的查询，尤其是对于日期可能重叠的汽车。
E、 g汽车结果
SELECT
`booked_until` AS `available-from`,
`booked_from` as `available-until`
FROM
booking
/** some JOIN magic? **
WHERE part_id IN(1,2,3)
E、 轮胎1的g与（1,2）中的零件id相同，因为轮胎2（id:3）与轮胎1没有直接关系。
双方应分别返回：
# car
| available-from | available-until |
------------------------------------
| NULL | 2021-07-31 |
| 2021-08-06 | 2021-08-09 |
| 2021-08-10 | NULL |
# tire 1
| available-from | available-until |
------------------------------------
| NULL | 2021-07-31 |
| 2021-08-07 | NULL |
其中NULL-值只是表示之前或之前没有任何预订。例如，从现在到2021-07-31和从2021-08-07直到地球存在的最后一天，此轮胎可用。
希望这是有意义的，并且有人能够提供帮助。
提前谢谢你。
共有3个答案
好的，所以我必须进一步扩展解决方案。添加了另一个表，该表为每个项目的每个条目保留特定数据。E、 g需要返回的时间以及将“ting”准备回其原始状态所需的时间。这只是一个近似值，不太重要，但它应该限制任何人在某个时间之前来收集它。：）
现在的表是这样的：
# tbl: property_unit (former: part)
| id | parent_id | identifier |
# tbl: property_unit_calendar (NEW)
| id | property_unit_id | return_by | preparation_time |
# tbl: property_unit_calendar_entry (former: booking)
| id | calendar_id | entry_start | entry_end |
这对当前查询没有太大影响，因为从表property\u unit\u calendar中的列return\u by和preparation\u time中的时间在预订时应用于property\u unit\u calendar\u条目中的日期时间字段。
采用代码并添加此关系。它似乎工作得很好-（再次感谢你这么多）。
更新后的数据库
现在，我正在努力找出应该如何减少结果以匹配我正在检查可用性的实际单元。我应该为每个选项卡添加选项吗？这样地
FROM tab
WHERE property_unit = 8
UNION
SELECT DISTINCT
...
FROM tab
WHERE property_unit = 8
另一件我正在努力解决的事情是如何将结果减少到某个框架，例如在日期之间、从某个日期开始或直到某个日期。
主要的问题不是如何在日期之间/从/直到日期获得结果，但如果预订时间早于时间范围，则第一行和最后一行中的任何一种方式都会出现空值，这应该表明它适用于“不可预测的过去/未来”（这可能不正确）。
因此......最好为NULL添加一个额外的查询来检查框架之前或之后是否有预订？
希望我解释得足够好。要考虑的依赖太多了！
谢谢
非常感谢你——你帮了大忙。
由于这个问题对我来说非常复杂，无法完全理解，我想问一下
- 如何仅选择unit\u id=1的结果？我尝试在外部查询中使用where语句，但没有任何效果。当在（1,2,3）中指定unit\u id时，返回所有日历相交的第一单元的结果就足够了（这将是db中为第一单元返回的行）
unit_id available_from available_til
---------------------------------------------------
1 (null) 2021-07-31 00:00:00
1 2021-08-07 00:00:00 2021-08-09 00:00:00
1 2021-08-10 00:00:00 (null)
再次感谢大家！
编辑：如果我在等式中添加“螺栓”（安装轮胎），给层次结构另一个层次。这个解决方案还有效吗？
Whole
_______|_______
| |
Part 1 Part 2
______|____ ...
| |
Bolt 1 .... Bolt N
好的，这是我的尝试。
因此，如果我理解正确，除了表中明确给出的信息外，还有机组的隐含不可用性。所以我首先明确地检索到：
select unit_id, entry_start, entry_end
from unit_calendar_entry
union
select p.id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.parent_unit_id = u.unit_id
union
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
order by unit_id, entry_start;
- 第一个
选择只是获取表中已经存在的条目 - 第二个添加了汽车条目，因为如果它的任何一个轮胎被预订，它可以被视为预订
- 第三个添加了轮胎条目，因为如果汽车被预订，它们可以被视为预订
结果：
unit_id entry_start entry_end
----------------------------------------------------
1 2021-07-31 00:00:00 2021-08-03 00:00:00
1 2021-08-03 00:00:00 2021-08-07 00:00:00
1 2021-08-04 00:00:00 2021-08-06 00:00:00
1 2021-08-09 00:00:00 2021-08-10 00:00:00
2 2021-07-31 00:00:00 2021-08-03 00:00:00
2 2021-08-03 00:00:00 2021-08-07 00:00:00
3 2021-07-31 00:00:00 2021-08-03 00:00:00
3 2021-08-04 00:00:00 2021-08-06 00:00:00
3 2021-08-09 00:00:00 2021-08-10 00:00:00
基于此，为了对相邻/重叠的时间跨度进行分组，需要解决一个间隙和孤岛问题。您可以使用两个查询来标记属于一起的条目，就像在这个SO答案中一样。如果我们调用上面的查询subtab，根据语句是
select c.*, sum(case when prev_end < entry_start then 1 else 0 end) over (order by unit_id, entry_start) as grouping
from (
select subtab.*, max(entry_end) over (partition by unit_id order by entry_start rows between unbounded preceding and 1 preceding) as prev_end
from subtab
) c
- 内部查询获取每行的上一个结束
- 外部分配一个分组id（在每个单元id内）标识属于连续块（也称为岛）的所有条目
结果：
unit_id entry_start entry_end prev_end grouping
-------------------------------------------------------------------------------
1 2021-07-31 00:00:00 2021-08-03 00:00:00 (null) 0
1 2021-08-03 00:00:00 2021-08-07 00:00:00 2021-08-03 00:00:00 0
1 2021-08-04 00:00:00 2021-08-06 00:00:00 2021-08-07 00:00:00 0
1 2021-08-09 00:00:00 2021-08-10 00:00:00 2021-08-07 00:00:00 1
2 2021-07-31 00:00:00 2021-08-03 00:00:00 (null) 1
2 2021-08-03 00:00:00 2021-08-07 00:00:00 2021-08-03 00:00:00 1
3 2021-07-31 00:00:00 2021-08-03 00:00:00 (null) 1
3 2021-08-04 00:00:00 2021-08-06 00:00:00 2021-08-03 00:00:00 2
3 2021-08-09 00:00:00 2021-08-10 00:00:00 2021-08-06 00:00:00 3
从这里（让我们称之为tab），您可以通过在上分组unit_id和分组来获取不可用的时间跨度（参见此SO答案或db
select distinct unit_id
, NULLIF((min(ifnull(prev_end,'1000-01-01')) over (partition by unit_id, grouping)),'1000-01-01') as available_from
, min(entry_start) over (partition by unit_id, grouping) as available_til
from tab
union
select distinct unit_id
, max(entry_end) over (partition by unit_id) as available_from
, null as available_til
from tab
order by unit_id, available_from
- 第一个查询从中获取可用的，作为每个单元id/
分组的上一个端的最小值。为了从MIN（）中获取值，我使用了类似于这个SO答案的解决方法
结果：
unit_id available_from available_til
---------------------------------------------------
1 (null) 2021-07-31 00:00:00
1 2021-08-07 00:00:00 2021-08-09 00:00:00
1 2021-08-10 00:00:00 (null)
2 (null) 2021-07-31 00:00:00
2 2021-08-07 00:00:00 (null)
3 (null) 2021-07-31 00:00:00
3 2021-08-03 00:00:00 2021-08-04 00:00:00
3 2021-08-06 00:00:00 2021-08-09 00:00:00
3 2021-08-10 00:00:00 (null)
将其全部放在一个查询中：
with tab as (
select c.*, sum(case when prev_end < entry_start then 1 else 0 end) over (order by unit_id, entry_start) as grouping
from (
select d.*, max(entry_end) over (partition by unit_id order by entry_start rows between unbounded preceding and 1 preceding) as prev_end
from (
select unit_id, entry_start, entry_end
from unit_calendar_entry
union
select p.id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.parent_unit_id = u.unit_id
union
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
) d
) c
)
select distinct unit_id, NULLIF((min(ifnull(prev_end,'1000-01-01')) over (partition by unit_id, grouping)),'1000-01-01') as available_from, min(entry_start) over (partition by unit_id, grouping) as available_til
from tab
union
select distinct unit_id, max(entry_end) over (partition by unit_id) as available_from, null as available_til
from tab
order by unit_id, available_from
另见本数据库
-
我是新来laravel的，所以原谅我可能的愚蠢问题。此外，我确实研究了所有其他“类似”的问题，但要么它们没有重现正确的解决方案，要么我真的很难理解。 情景： 我有一个帖子模型和一个主题模型。这就是他们现在的样子。 在岗位上。php 在主题上。php 现在，我需要实现的是： 如果将查询参数传递给请求（即q=Food），我只想返回在主题关系中包含主题食物的帖子，而不返回其他帖子。如果什么都没有通过，那
-
问题内容： 有三个表：，并且，医院能提供的医疗服务和语言服务。因此，存在两个多对多关系。 简单ERD 现在，我想使用和搜索医院数据。 DaoImpl： 而且，如果我想从三个表中按邮政编码，医疗类型和语言进行搜索，那么如何编写一个jsql。 警告： 错误：org.hibernate.hql.internal.ast.ErrorCounter- 预期加入的路径！希望加入的路径！在org.hiberna
-
我有一个桌子实验和一个桌子标签。一个实验可能有许多标签。架构： 是否可以使用返回实验和相应标签列表的jooq创建查询？ 类似
-
我需要支持一个涉及以下实体的场景（使用JPA）： 用户 一个用户可以有多个帐户，一个帐户可以在多个用户之间共享，这是迄今为止的标准@ManyToMany关系。 一个用户可以为每个帐户拥有一组不同的角色，一个角色可以在多个用户之间共享。 我遵循了这个实践，它解释了一种用额外列映射多对多关联的方法，但我不确定我是否得到了它。 用户实体： 账户实体： 用户帐户实体： 用户帐号： 我正在创建一个新用户并尝
-
问题内容： 我有一个类似（简化）的表结构： 内容 content_has_content topic_has_content 任何主题都可以具有多个“内容”，任何“内容”都可以具有多个“子内容”（内容实例）。对于给定的id_topic，我想从链接的内容，子内容，子内容的子内容等中接收所有my_string1的列表。 我了解“ WITH”不适用于mysql，但找不到很好的递归替代方法。 谢谢丹尼尔
-
问题内容： Book，User和Review说，我正在构建具有复杂模型的应用程序。 评论包含书籍和用户ID。为了能够搜索至少包含一个评论的“图书”，我已将“图书”设置为“评论”的父级，并且具有这样的路由。但是，我还需要找到撰写包含某些短语的评论的用户。 是否可以同时将书和用户作为评论的父级？有没有更好的方法来处理这种情况？ 请注意，我无法更改数据建模的方式/不愿意这样做，因为数据已从持久性数据库传