mysql查找前一天不存在行的日期

Question

我需要选择自数据中断以来的天数。更容易显示:

表格格式:

id (autoincrement), user_id (int), start (datetime), end (datetime)

示例数据(时间被遗漏，因为只需要几天):

1, 5, 2011-12-18, 2011-12-18
2, 5, 2011-12-17, 2011-12-17
3, 5, 2011-12-16, 2011-12-16
4, 5, 2011-12-13, 2011-12-13

如您所见，2011-12-13 和 2011-12-16 之间会有一个中断。现在，我需要能够说:

使用日期 2011-12-18，距离休息还有多少天:

2011-12-18: Lowest sequential date = 2011-12-16: Total consecutive days: 3

可能:DATE_DIFF(2011-12-18, 2011-12-16)

所以我的问题是，如何选择 2011-12-16 是最低的连续日期？请记住，数据适用于特定的 user_id。

有点像这里的例子:http://www.artfulsoftware.com/infotree/queries.php#72但反过来。

我希望只用 SQL 完成，不需要 php 代码

谢谢

Answer 1

SELECT qmin.start, qmax.end, DATE_DIFF( qmax.end, qmin.start ) FROM table AS qmin
  LEFT JOIN ( 
    SELECT end FROM table AS t1
      LEFT JOIN table AS t2 ON 
        t2.start > t1.end AND 
        t2.start < DATE_ADD( t1.end, 1 DAY )
      WHERE t1.end >= '2011-12-18' AND t2.start IS NULL
      ORDER BY end ASC LIMIT 1 
  ) AS qmax
  LEFT JOIN table AS t2 ON 
    t2.end < qmin.start AND 
    t2.end > DATE_DIFF( qmin.start, 1 DAY )
  WHERE qmin.start <= '2011-12-18' AND t2.start IS NULL
  ORDER BY end DESC LIMIT 1 

这应该有效 - 左连接选择一个可以按顺序排列的日期，因此如果您采用没有顺序记录的最近记录( t2.anyfield 为 null )，则 max 可以被罚款，这与我们对最小日期所做的事情相同。

如果您可以在脚本中计算天数 - 使用并集(例如 1.row - 最小，2.row 最大)