php – 如何检查mysql日期列中的连续天数

发布时间：2020-12-13 22:53:19 所属栏目：PHP教程来源：网络整理

导读：有一个 MySQL表列出了健康训练. recordId (primary key,integer,auto incrementing)workoutNumber (integer)date (date,ex- "2014-07-29") 我需要知道用户最近连续几天的工作时间.我们可以在MySQL查询中执行此操作吗？我使用PHP作为应用程序语言. 解决方法

有一个 MySQL表列出了健康训练.

recordId (primary key,integer,auto incrementing)
workoutNumber (integer)
date (date,ex- "2014-07-29")

我需要知道用户最近连续几天的工作时间.我们可以在MySQL查询中执行此操作吗？我使用PHP作为应用程序语言.

解决方法

我可以使用临时变量给你一个“纯SQL”解决方案：

此查询将创建一个连续几天为1的列,如果日期不连续则为0.

select a.*,coalesce(date_diff(a.date,@prevDate),0) = 1 as consecutiveDay -- '1' if the days are consecutive,-- '0' otherwise
                                                                      -- (The first row will be 0),@prevDate := a.date as this_date -- You need to store the date of the current record
                                       -- to compare it with the next one
from
    (select @prevDate := null) as init  -- This is where you initialize the temp 
                                        -- variable that will track the previous date,yourTable as a
-- WHERE -- (Put any where conditions here)
order by a.date;

现在,您可以将使用上述查询的那些作为第二个查询的行源进行求和：

select sum(consecutiveDays) as consecutiveDays
from 
    ( select a.*,0) = 1 as consecutiveDay,@prevDate := a.date as this_date
      from (select @prevDate := null) as init,yourTable as a
      -- WHERE -- (add where conditions here)
      order by a.date
    ) as b

希望这可以帮助

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!