mysql - 基于日期的最后连续插入记录数

Question

我想获取最后连续插入的记录数。

我有表格

user_id | point |   DateTime
1       |   10  |   18-08-2018 17:15
2       |   10  |   01-08-2018 17:15
1       |   10  |   21-08-2018 17:15
1       |   10  |   22-08-2018 17:15
2       |   10  |   26-08-2018 17:15
1       |   10  |   25-08-2018 17:15
2       |   10  |   27-08-2018 17:15
1       |   10  |   26-08-2018 17:15
1       |   10  |   27-08-2018 17:15

现在 user_id = 1 有 6 条记录

但是我想统计前一个日期的最后连续记录

所以预期的输出应该是count

3

因为

27-08-2018
26-08-2018
25-08-2018

以上日期是连续的，然后在 25-08-2018 到 22-08-2018 之间有一个间隔

如果 24-08-2018 存在另一条记录，则输出应为

4

这是我的 SQLFiddle

Answer 1

首先，你需要让DateTime列为datetime类型

这是一个差距和孤岛问题，您可以尝试通过 user_id 和每个 min(datetime) 的不同天数来制作行号> 来自每个 user_id。

CREATE TABLE Table1
    (`user_id` varchar(12), `point` varchar(9), `DateTime` datetime)
;

INSERT INTO Table1
    (`user_id`, `point`, `DateTime`)
VALUES
    ('1', '10', '2018-08-18 17:15'),
    ('2', '10', '2018-08-01 17:15'),
    ('1', '10', '2018-08-21 17:15'),
    ('1', '10', '2018-08-22 17:15'),
    ('2', '10', '2018-08-26 17:15'),
    ('1', '10', '2018-08-25 17:15'),
    ('2', '10', '2018-08-27 17:15'),
    ('1', '10', '2018-08-26 17:15'),
    ('1', '10', '2018-08-27 17:15')

;

查询 1:

SELECT cnt
FROM (
  SELECT rn,MAX(DateTime) mxDt,COUNT(*) cnt
    FROM (
      SELECT DateTime,datediff(DateTime,mDt) -(SELECT COUNT(*) 
                FROM Table1 tt 
                WHERE t2.DateTime >= tt.DateTime 
                and t2.user_id = tt.user_id
               ) rn
      FROM (
        SELECT user_id,MIN(DateTime) mDt
        FROM Table1
        GROUP BY user_id
      ) t1 INNER JOIN Table1 t2 on t1.user_id = t2.user_id
      Where t1.user_id = 1 ##set your expect select user_id
    ) t1
  GROUP BY rn
)t1 
ORDER BY mxDt desc
LIMIT 1

Results :

| cnt |
|-----|
|   3 |