时间戳

首页 > 解决方案 > 从同一个表中重叠的唯一天数列表

mysql - 从同一个表中重叠的唯一天数列表

问题描述

这是我的 MySQL 表：

ID    start       end
====  =====       ===
01    01/01/2020  10/01/2020
02    09/01/2020  31/01/2020
03    02/01/2020  04/01/2020

我正在尝试获取与自己重叠的唯一日期列表，因此这是预期的结果：

results
=====
02/01/2020
03/01/2020
04/01/2020
09/01/2020
10/01/2020

我尝试使用“between”来获得它，但它只返回一个日期范围。这是我的查询：

SELECT t1.* 
  FROM $table AS t1, $table AS t2 
 WHERE t1.id > t2.id 
   AND (t1.check_in_date BETWEEN t2.check_in_date AND t2.check_in_date 
    OR  t1.check_out_date BETWEEN t2.check_in_date AND t2.check_out_date 
    OR  t1.check_in_date >= t2.check_in_date AND t1.check_out_date <= t2.check_out_date)

我可以扩展此查询以实现我的目标还是需要我找到完全不同的方式？

更新（从评论中复制）

我正在使用 MariaDB v.10.0.44

标签： mysqlsqldatetime

解决方案

WITH RECURSIVE
cte AS ( SELECT MIN(`start`) `date`
         FROM sourcetable
       UNION ALL
         SELECT `date` + INTERVAL 1 DAY
         FROM cte
         WHERE `date` < ( SELECT MAX(`end`)
                          FROM sourcetable )
       )
SELECT cte.`date`
FROM cte
JOIN sourcetable src ON cte.`date` BETWEEN src.`start` AND src.`end`
GROUP BY cte.`date`
HAVING COUNT(*) > 1;

小提琴

在 MySQL 5.x 中，您可以使用以下技术：

SELECT cte.`date`
FROM ( SELECT min_date.min_start + INTERVAL n1.n*10+n2.n DAY `date`
       FROM ( SELECT MIN(sourcetable.`start`) min_start
              FROM sourcetable ) min_date,
            ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION  SELECT 3 UNION SELECT 4 
              UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION  SELECT 8 UNION SELECT 9 ) n1,
            ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION  SELECT 3 UNION SELECT 4 
              UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION  SELECT 8 UNION SELECT 9 ) n2
     ) cte
JOIN sourcetable src ON cte.`date` BETWEEN src.`start` AND src.`end`
GROUP BY cte.`date`
HAVING COUNT(*) > 1;

小提琴

上面的查询假设表中最小日期和最大日期之间的差异不超过 99 天。如果它可能更大，则必须使用适当的表达式版本添加更多数字生成的子查询。

推荐阅读