如何获取表中连续记录之间的天数? [英] how to get number of days between consecutive records in table?

问题描述

在MySQL中，我有一个包含几列的表，其中一列是日期列. 我希望能够进行查询，这样我可以得到一个附加列，该列显示当前日期(每行的日期)和上一行的日期之间的天数.

In MySQL, I have a table with several columns, one of them being a date column. I want to be able to make a query so i can get an additional column showing the number of days between the current date (the date for each row) and the date of the previous row.

记录的顺序是按日期，而我是按其他列过滤的.

The order of records is by date, and I filter by other column.

例如

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.

Obviously since the first row has nothing to compare to, it should show 0.

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

I know i can get the diff between two dates with the datediff MySQL function, e.g.

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

我的基本查询是select * from mytable where other_col = 'something' order by date; 但是如何进行查询以直接获得示例中所示的结果? (我需要额外的"days_between_dates"列).

My basic query is select * from mytable where other_col = 'something' order by date; but how can i make a query to directly obtain a result like in the example ? (I need the extra "days_between_dates" column).

还请注意，通常ID是有序的，没有空格，但我不能保证.只是我将按升序排序，并按"other_col"列进行过滤.

Also note that usually the id's are ordered, and without gaps, but i cannot guarantee that. only that i will sort by ascending date, and filtering by the "other_col" column.

推荐答案

您可以按照以下步骤进行操作:

You can do something along these lines:

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)和小于当前记录的日期(WHERE TT.date < T.date)中最大记录的日期(MAX(TT.date))之间的差来实现的.

This works by calculating the difference between the current record's date (T.date) and the date that is the biggest (MAX(TT.date)) of the one's that are smaller than the current record's date ( WHERE TT.date < T.date ).

当没有更小的日期时，会使用COALESCE将其设置为0，而不是null.

When there is no smaller date, there's the COALESCE to make it be 0, instead of null.

编辑.在第一行中添加合并以给出0

Edit. added coalesce to give 0 in the first line

这篇关于如何获取表中连续记录之间的天数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！