MySQL:计算在给定日期之前具有连续日期的行

Question

我有一张记录员工工作类次的表格。它非常简单，包括(为简单起见进行了缩减):一个自动递增的主键、employee_id、job_id、date

为每个工作类次创建一行，因此如果特定员工在特定日期不工作，则该员工在该日期不会有行。

对于给定的一天，我需要能够返回每位员工在这一天之前的连续工作天数。

我不知道从哪里开始，希望得到任何帮助。谢谢

Answer 1

如果您有一个数字序列，那么这些数字与日期序列之间的差异将是恒定的。我们可以使用这个事实将工作日分组为具有最小和最大日期的组:

select employee_id, min(date) as mindate, max(date) as maxdate
from (select s.*, date_sub(date, interval seqnum days) as grp
      from (select s.*, (@rn := @rn + 1) as seqnum
            from shifts s cross join
                 (select @rn := 0) const
           ) s
     ) s
group by employee_id, grp;

一旦我们有了这个，就很容易得到特定员工到特定日期的时间长度:

select employee_id,
       coalesce(datediff(XXX, 1+min(case when XXX between mindate and maxdate then mindate end)),
                0) as SequentialWorkingDays
from (select employee_id, min(date) as mindate, max(date) as maxdate
      from (select s.*, date_sub(date, interval seqnum days) as grp
            from (select s.*, (@rn := @rn + 1) as seqnum
                  from shifts s cross join
                       (select @rn := 0) const
                 ) s
           ) s
      group by employee_id, grp
     ) s
group by employee_id;