使用MySQL 5.6查找重叠的日期范围记录？

发布于 2025-01-19 17:41:16 字数 720 浏览 4 评论 0原文

我有一个带有日期范围和场地ID的活动列表。我需要同时找到不同主动场所的最大计数。

create table if not exists events
(
    eventstart datetime,
    eventend datetime,
    venue int
);

INSERT INTO events VALUES ('2022-04-06 18:00:00', '2022-04-06 19:30:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:15:00', '2022-04-06 21:00:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:45:00', '2022-04-06 20:00:00', 2);
INSERT INTO events VALUES ('2022-04-06 18:30:00', '2022-04-06 19:00:00', 3);

https:///www.db-fiddle.com/ >

在上一个示例中，我期望3个结果，因为从18.45-19.00起3个同时活跃的并发不同的场所。

我找不到写此查询的聪明方法。

原文

I have a list of events with date-range and venue id.
I need to find the max count of distinct active venues at the same time.

create table if not exists events
(
    eventstart datetime,
    eventend datetime,
    venue int
);

INSERT INTO events VALUES ('2022-04-06 18:00:00', '2022-04-06 19:30:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:15:00', '2022-04-06 21:00:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:45:00', '2022-04-06 20:00:00', 2);
INSERT INTO events VALUES ('2022-04-06 18:30:00', '2022-04-06 19:00:00', 3);

https://www.db-fiddle.com/f/a2PRXG2WQ3samrWb66fimv/4

With the previous example, I expect a result of 3 because from 18.45-19.00 there 3 concurrent distinct venues active at the same time.

I cannot find a smart way to write this query.

分享到QQ

分享到微博