MySQL时间范围(不包括重叠的时间范围)

Question

结束日期2021-08-10 15:25:100000

id	开始日期
7	2021-08-07 15:25:100000
8	2021-08-09 15:25:100000	2021-8-12 15:25:100000
9	2021-08-19 15:25:100000	2022-08-22 15:25:100000
10	2021-08-21 15:25:100000	2021-8-25 15:25:100000

一个想法：

列出没有记录的最短和最长日期，然后将其作为一个单独的组在之前和之后(无记录日期范围)进行区分。

*有关完整的细分，请参阅此fiddle链接：https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=d366ff58efc1f520132ed7cd81d729de

细分：

1. 根据表中的现有数据生成一系列日期。幸运的是，MariaDB 10.4支持WITH RECURSIVE的窗口功能，因此，一旦您了解了WITH RECURSIVE的工作原理，就可以很容易地生成日期范围：

WITH RECURSIVE cte AS (
SELECT MIN(start_date) AS dt, MAX(end_date) AS mxdt FROM deneme UNION ALL
SELECT dt+INTERVAL 1 DAY, mxdt FROM cte WHERE dt+INTERVAL 1 DAY <= mxdt)
SELECT * FROM cte;

2. 将生成的日期范围和LEFT JOIN与表deneme一起使用，只获取没有记录的日期，获取其中的MIN()和MAX()日期：

WITH RECURSIVE cte AS (
SELECT MIN(start_date) AS dt, MAX(end_date) AS mxdt FROM deneme UNION ALL
SELECT dt+INTERVAL 1 DAY, mxdt FROM cte WHERE dt+INTERVAL 1 DAY <= mxdt)

SELECT MIN(dt) min_no_record, MAX(dt) max_no_record
FROM cte 
LEFT JOIN deneme d1 ON dt BETWEEN d1.start_date AND d1.end_date
WHERE d1.start_date IS NULL;

3. 将上面的查询作为子查询，并再次将CROSS JOIN与表deneme一起使用，但这次使用CASE表达式来分配不存在的记录周期前后的每个日期：

WITH RECURSIVE cte AS (
SELECT MIN(start_date) AS dt, MAX(end_date) AS mxdt FROM deneme UNION ALL
SELECT dt+INTERVAL 1 DAY, mxdt FROM cte WHERE dt+INTERVAL 1 DAY <= mxdt)

SELECT *,
CASE WHEN d.end_date < min_no_record THEN min_no_record
WHEN d.start_date > max_no_record THEN max_no_record
ELSE 0 END
FROM deneme d 
CROSS JOIN   
(SELECT MIN(dt) min_no_record, MAX(dt) max_no_record
FROM cte 
LEFT JOIN deneme d1 ON dt BETWEEN d1.start_date AND d1.end_date
WHERE d1.start_date IS NULL) v;

*CASE表达式中... THEN min_no_record或... THEN max_no_record的部分可以是任何东西。您可以将其定义为... THEN 1或... THEN 2，但这取决于您自己。

4. 最后，得到该组的MIN()和MAX()日期。在CCD_ 17中使用来自上面的CCD_ 16表达式；CCD_ 18中省略：

WITH RECURSIVE cte AS (
SELECT MIN(start_date) AS dt, MAX(end_date) AS mxdt FROM deneme UNION ALL
SELECT dt+INTERVAL 1 DAY, mxdt FROM cte WHERE dt+INTERVAL 1 DAY <= mxdt)
SELECT MIN(d.start_date),
MAX(d.end_date)
FROM deneme d 
CROSS JOIN
(SELECT MIN(dt) min_no_record, MAX(dt) max_no_record
FROM cte 
LEFT JOIN deneme d1 ON dt BETWEEN d1.start_date AND d1.end_date
WHERE d1.start_date IS NULL) v
GROUP BY CASE WHEN d.end_date < min_no_record THEN min_no_record
WHEN d.start_date > max_no_record THEN max_no_record
ELSE 0 END;