如何获取表中连续记录之间的天数？

Question

在MySQL中，我有一个表格包含多个列，其中一个是日期列。我想要查询，以便能够得到一个额外的列，显示当前行的日期与上一行日期之间的天数。

记录按照日期排序，并且我会根据其他列进行筛选。

例如：

id  other_col   date        days_between_dates
1   abc     2013-05-01      0
2   abc     2013-05-07      6
3   abc     2013-05-09      2
4   abc     2013-05-19      10
5   abc     2013-05-25      6
6   abc     2013-06-03      9
7   abc     2013-06-14      11

显然，由于第一行没有可比较的内容，应该显示0。

我知道可以使用datediff MySQL函数获取两个日期之间的差异，例如：

select datediff('2013-06-03','2013-05-25');

我的基本查询是select * from mytable where other_col = 'something' order by date; 但我怎么样才能直接获得像例子中那样的结果呢？（我需要额外的"days_between_dates"列。）

另请注意，通常id是有序的，并且没有间隔，但我无法保证。只有按升序日期排序，并过滤“other_col”列。

- DiegoDD

---

表中的ID是否保证是连续的（即它们是自增值，您不会删除行或更改自增值）？ - Mike Brant

是的，它们是顺序的，但我不知道它们是否可以被删除，从而在ID中产生“间隙”。理想情况下（并且我看到的所有情况都是这样），ID始终是连续的且没有间隙，但可能并非总是如此。对于这种情况，让我们假设它们确实是连续的。 - DiegoDD

Answer 1

您可以按照以下方式进行操作：

SELECT T.id, 
       T.other_col, 
       T.yourdate, 
       IFNULL(datediff(T.yourdate,(select MAX(TT.yourdate) 
                                   FROM yourtable TT 
                                   WHERE TT.yourdate < T.yourdate 
                                   AND TT.other_col = 'abc')),0)
FROM yourtable T
where other_col = 'abc';

您可以在此示例中看到它。

这是通过计算当前记录的日期(T.date)与小于当前记录日期的最大日期(MAX(TT.date))之间的差来实现的(WHERE TT.date < T.date)。

当没有更小的日期时，使用COALESCE将其变为0，而不是null。

编辑。添加了COALESCE以在第一行中给出0。

- Filipe Silva

---

似乎它能够工作！您介意进一步解释吗？特别是DATEDIFF第二个参数内部的SELECT以及为什么使用MAX日期。 - DiegoDD

更新一下，当我有几个不同的"other_col"值，并且它们之间的日期不连续时，它就不能正常工作，详见此链接（http://sqlfiddle.com/#!2/cdef8/1/0）。我想我只需要在两个选择中都加上`where`条件。让我试试。 - DiegoDD

是的，你必须这样做，否则内部查询将在整个表中检查最大日期小于当前记录日期的日期。如果你把where语句放在内部查询中，它会按照你想要的方式工作。 关于这种工作方式，我会在我的答案中更新一个简短的解释。 - Filipe Silva

谢谢，如果您不介意的话，在答案中也加上“where”条件，这样其他人无论评论与否都可以看到它们，同样的方式也适用于fiddle（所以如果我接受它，它就是一个完整的答案）=）。此外，我会使用ifnull()函数而不是那个“coalesce”函数，因为它更容易理解。再次感谢！ - DiegoDD

我创建了一个更新的fiddle，其中包含更多值和完整查询的更完整示例。 - DiegoDD

完成。您可能需要更新您的问题，将一些记录与 other_col = 'def' 相关联，并添加 SQL 标签，以便人们更容易地找到它。 - Filipe Silva

Answer 2

我想提供一个与上面给出的答案不同的选择。 如果您可以保证id是连续的，那么您可以将表格自身连接以获得您要查找的结果：

SELECT t1.id, t1.other_col, t1.date, DATEDIFF(t2.date,t1.date)
FROM table AS t1
LEFT_JOIN table AS t2 ON t1.id = t2.id - 1

请注意，我在这里使用了LEFT JOIN，这样t1（主表）的第一行和最后一行将会呈现。

- Mike Brant