MySQL:获取具有连续日期的行 - MySQL: get rows with consecutive dates

MySQL: get rows with consecutive dates

本文关键字：日期连续获取 MySQL | 更新日期: 2023-12-02

例如：

    id | date
   ---+------------
    1 | 2011-01-01
    2 | 2011-01-02
    3 | 2011-01-15
    4 | 2011-01-20
    5 | 2011-01-10
    6 | 2011-01-30
    7 | 2011-01-03
    8 | 2011-02-09
    9 | 2011-02-10
    10 | 2011-02-11
    11 | 2011-02-12
    12 | 2011-02-15
    13 | 2011-02-19
    14 | 2011-02-21
    15 | 2011-02-22
    16 | 2011-02-23
    17 | 2011-02-24

例如，下面是一些行（日期现在可能总是按顺序排列）。多个日期相同的记录是一种可能的情况。我想要连续日期的行。例如；对于以上条目，我想要类似的东西：

   id | date
   ---+------------
    1 | 2011-01-01
    2 | 2011-01-02
    7 | 2011-01-03
    8 | 2011-02-09
    9 | 2011-02-10
    10 | 2011-02-11
    11 | 2011-02-12
    14 | 2011-02-21
    15 | 2011-02-22
    16 | 2011-02-23
    17 | 2011-02-24

我试过类似的东西：

SELECT a.id, a.date FROM tbl a
LEFT JOIN tbl b on a.user_id = b.user_id AND a.date = b.date + INTERVAL 1 DAY

但这并没有给我合适的结果，而且速度也慢得多。所以请指导我一些正确的流程和更快的速度。

注意：这实际上是一个考勤计划，所以多个用户应该有相同日期的记录。因此，我需要所有用户的所有具有连续日期的记录

首先在日期列上创建一个索引：

create index idx_tbl_date on tbl(date);

然后，我认为以下将做你想要的：

select t.*
from tbl t
where exists (select 1 from tbl t2 where t2.date = t.date + interval 1 day) or
      exists (select 1 from tbl t2 where t2.date = t.date - interval 1 day);

这两个exists子句确保您获得序列中的第一个和最后一个日期。有了索引，这应该表现得很好。

SELECT a.id, a.date FROM a
LEFT JOIN b on a.id = b.id AND a.date <= b.date + INTERVAL '1 day'
ORDER BY a.date;

语法INTERVAL 1 DAY是错误的，我想你在找INTERVAL '1 day'。此外，如果我正确理解你的问题，而我可能不理解，你可能想使用<=而不是=来检查日期是否在1天内。希望这能有所帮助。

这并不重要，因为总是很难有第一行或最后一行。这是

 mysql> SET @a:=NULL;
       SELECT  DISTINCT m.id, m.date
       FROM mytable3 m,
           (SELECT IF(date<=DATE_ADD(@a, INTERVAL 1 DAY), @a, NULL) start,
                  @a:=date next  
                  FROM mytable3 ORDER BY date) m2
       WHERE m.date=m2.start 
            OR (m.date=m2.next AND m2.start IS NOT NULL);

查询正常，0行受影响（0.00秒）

 +----+------------+
 | id | date       |
 +----+------------+
 |  1 | 2011-01-01 |
 |  2 | 2011-01-02 |
 |  7 | 2011-01-03 |
 |  8 | 2011-02-09 |
 |  9 | 2011-02-10 |
 | 10 | 2011-02-11 |
 | 11 | 2011-02-12 |
 | 14 | 2011-02-21 |
 | 15 | 2011-02-22 |
 | 16 | 2011-02-23 |
 | 17 | 2011-02-24 |
 +----+------------+

一组11行（0.08秒）

如果你不喜欢使用变量，你也可以使用以下方法：

       SELECT  DISTINCT m.id, m.date
       FROM mytable3 m,
           (SELECT m1.date start, m2.date next FROM mytable3 m1, mytable3 m2
            WHERE m2.date<=DATE_ADD(m1.date, INTERVAL 1 DAY) AND m2.date>m1.date) m2
       WHERE m.date=m2.start 
            OR (m.date=m2.next AND m2.start IS NOT NULL);