如何在mysql表中查找丢失的行(日期)? [英] How to find missing rows (dates) in a mysql table?

问题描述

我已经尝试过多个类似的主题:如何查找缺失的主题数据行使用SQL?在这里，但我无法使其在我的情况下起作用.

I have tried several topics like this one: How to find missing data rows using SQL? here, but I couldn't make it work in my situation.

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

I have a table named posts in MySQL which I save user diaries in it every day. Sometimes users forget to write a post for a day and I want to make it possible for them to submit it later. So the db structures like this:

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

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

So I want to show a dropdown list of missing dates in this table of missing rows to user, so he can select the date he wants to submit the post for.

我该怎么做? 谢谢.

推荐答案

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

These types of queries are easiest to solve if you have a date table. In your DB, run this batch as a one-off to create a filled date table.

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

Then you can just use a regular LEFT JOIN

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

当然，您不希望所有2000年至2030年的缺失"日期.将其限制为帖子表(针对用户)中的MIN和MAX日期，即

Of course you don't want all "missing" dates from 2000 to 2030. Limit it to the MIN and MAX dates in the posts table (for the user), i.e.

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

这篇关于如何在mysql表中查找丢失的行(日期)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！