mysql - 用于将日期分组为括号开始和结束组的 Sql 查询

Question

我的数据如下表

+------------+----------+------------+
| Event name |Date      |Action      |
+------------+----------+------------+
| Event A    |10/08/2018| Started    | 
| Event B    |10/08/2018| Started    | 
| Event A    |11/08/2018| Ended      | 
| Event B    |12/08/2018| Ended      | 
| Event A    |13/08/2018| Started    | 
| Event A    |14/08/2018| Ended      | 
+------------+----------+------------+

我正在尝试编写一个查询，我想在其中列出事件正在进行的所有日期。

    +------------+----------+------------+
    | Event name |Date      |Status      |
    +------------+----------+------------+
    | Event A    |10/08/2018| Ongoing    | 
    | Event A    |11/08/2018| Ongoing    | 
    | Event A    |12/08/2018| null       | 
    | Event A    |13/08/2018| Ongoing    | 
    | Event A    |14/08/2018| Ongoing    | 
    | Event A    |15/08/2018| null       | 
    | Event B    |10/08/2018| Ongoing    | 
    | Event B    |11/08/2018| Ongoing    | 
    | Event B    |12/08/2018| Ongoing    | 
    | Event B    |13/08/2018| null       | 
    | Event B    |14/08/2018| null       | 
    | Event B    |15/08/2018| null       | 
    +------------+----------+------------+

我能够通过获取开始时间的最小值和结束时间的最大值来找到一个连续范围，但需要有关如何将其分解为范围的帮助。

Answer 1

我可以建议以下查询:让我们调用您的表事件

我们需要做的第一件事是将其连接到自身，以使开始与结束位于同一行:

    SELECT * FROM events st 
INNER JOIN events end ON st.event = end.event AND end.status =  'ended' AND event.date < end. date
 WHERE st.status = 'started'

接下来，我们想要为每个开始获得最早的结束，为此我们将添加另一个左连接，通过日期差异和 NULL 过滤掉不是的连接我们只会得到正在进行的案例:

 SELECT st.event_name, st.date AS start_date, end.date AS end_date FROM events st 
INNER JOIN events end ON st.event_name = end.event_name AND end.status =  'ended' AND event.date < end.date
LEFT JOIN events er ON er.event = end.event_name AND er.status = 'ended'
AND er.date < end.date
 WHERE st.status = 'started'
AND er.event_name IS NULL

最后我们加入我们写入原始表的查询并获得您想要的输出:

    SELECT events.event_name, events.date, IF(ong.event_name IS NULL, 'not active', 'ongoing') AS status FROM events
LEFT JOIN (SELECT st.event_name, st.date AS start_date, end.date AS end_date FROM events st 
    INNER JOIN events end ON st.event_name = end.event_name AND end.status =  'ended' AND event.date < end.date
    LEFT JOIN events er ON er.event = end.event_name AND er.status = 'ended'
    AND er.date < end.date
     WHERE st.status = 'started'
    AND er.event_name IS NULL) ong ON
ong.event_name = events.event_name AND ong.start_date >= events.date AND events.date <= ong.end_date

请注意，只要相同的事件不在同一日期开始和结束，此方法就有效如果您的表保存确切出现的 DATETIME ，那就最好了