mysql 统计连续天数

 

以下为例子数据 图1

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　图1

 

 

 

首先根据要求取出BeforeMeal要在7.0以下 并且 bingAfterMeal要在11.1以下

select AccountId,CreateTime from DiabetesRecord where BeforeMeal < 7.0 and AfterMeal < 11.1 group by AccountId,CreateTime

 得到以下数据如图2

　　　　　　　　　　　　图2

 

 

把连续的天数进行分组

select *,((select count(1) from (select AccountId,CreateTime from DiabetesRecord where BeforeMeal < 7.0 and AfterMeal < 11.1 group by AccountId,CreateTime)dr2 where dr1.AccountId = dr2.AccountId and dr2.CreateTime <= dr1.CreateTime) - day(dr1.CreateTime)) as 'rownum' from (select AccountId,CreateTime from DiabetesRecord where BeforeMeal < 7.0 and AfterMeal < 11.1 group by AccountId,CreateTime)dr1

结果如图3所示

　　　　　　　　　　　　图3

 

 

按用户 、开始时间、结束时间 和 连续天数分组

 

select AccountId,min(CreateTime),max(CreateTime),(datediff(max(CreateTime),min(CreateTime))+1) as 'rn' from 
(select *,((select count(1) from (select AccountId,CreateTime from DiabetesRecord where BeforeMeal < 7.0 and AfterMeal < 11.1 group by AccountId,CreateTime)dr2 where dr1.AccountId = dr2.AccountId and dr2.CreateTime <= dr1.CreateTime) - day(dr1.CreateTime)) as 'rownum' from (select AccountId,CreateTime from DiabetesRecord where BeforeMeal < 7.0 and AfterMeal < 11.1 group by AccountId,CreateTime)dr1)z group by AccountId,rownum

得到如图4所示

　　　　　　　　　　　　　　　　图4