如何查找 mysql 表中缺失的行（日期）？

Question

我尝试过几个这样的主题：如何使用查找丢失的数据行SQL？ 在这里，但我无法让它在我的情况下工作。

我在 MySQL 中有一个名为 posts 的表，我每天都在其中保存用户日记。有时用户一天忘记写帖子，我想让他们稍后可以提交。 因此，数据库结构如下：

date           userid
2011-10-01     1
2011-10-02     1
(missing)
2011-10-04     1
2011-10-05     1
(missing)
2011-10-07     1

因此，我想向用户显示此缺失行表中缺失日期的下拉列表，以便他可以选择他想要提交帖子的日期。

我该怎么做？ 谢谢。

Answer 1

select  
    t0.date,
    t1.date1
    if(t1.date1 is null, date_add(t0.date, interval 1 day), '') missing_date
from
(select date from posts group by date) t0
left join
(select date_add(date, interval 1 day) date1 from posts group by date) t1
on t0.date = t1.date1
order by t0.date asc

您可以通过这种方式找到丢失的日期。请注意，您需要删除最后一行，您可以选择适合您的方式来实现这一目标。

Answer 2

查找缺失日期的最简单方法是使用日历表。我发布了 代码来创建和填充PostgreSQL 的日历表；你应该能够毫无困难地适应它。

有了日历表，您的查询就非常简单且易于理解。要查找 2011 年 10 月的缺失日期，您可以使用类似的方法。 （在你的“帖子”表中猜测。）

select c.cal_date
from calendar c
left join posts p on (c.cal_date = p.date)
where p.date is null
  and c.cal_date between '2011-10-01' and '2011-10-31'
  and p.userid = 1
order by c.cal_date

Answer 3

如果您有日期表，这些类型的查询最容易解决。
在您的数据库中，一次性运行此批处理以创建填充日期表。

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('2000-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x >= '2030-12-31' END REPEAT;
END//
delimiter ;

CALL FillDateTable;

然后你可以只使用常规的 LEFT JOIN

SELECT thedate
FROM datetable
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL

当然你不想要从 2000 年到 2030 年的所有“缺失”日期。将其限制为 posts 表中的最小和最大日期（对于用户），即

SELECT thedate
FROM datetable
INNER JOIN (select min(date) postStart, max(date) postEnd
            FROM posts
            where userid=123) p on datetable.thedate BETWEEN p.postStart and p.postEnd
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL

Answer 4

您可以每次（一天结束）自动输入一个空帖子，带有空标题，无空内容，但实际日期。然后，如果用户想添加前一天的帖子，请显示所有带有空的标题和内容的帖子，然后更新他选择的帖子。

这不是一个空间问题，如果他们写的比错过的要多。例如，如果他们写了4天而错过了1

。如果他们在X天尚未添加丢失的帖子，那么他们可能永远也不会。

如果我的解决方案微不足道 /太抽象，我深表歉意。