mysql如何查询某个店铺连续三天＞=100的数据

项目场景：

提示：这里简述项目相关背景：

例如：项目需要查询各个店铺连续3天销量>=100的数据

---

思路分析：

---

1.单表查出所需要的店铺、日期字段

select AIRS_NO,GROUND_DATE from ground_job where model = '某36' and sj_num = 1 GROUP BY AIRS_NO,GROUND_DATE

2.根据AIRS_NO匹配，相同数字+1,不同为1。用（t1.GROUND_DATE-t1.rk）相同则为相邻日期。

select t1.AIRS_NO,t1.GROUND_DATE,t1.ground_context,t1.rk,t1.GROUND_DATE-t1.rk as dis from (
select @rk:=case when @dept=a.AIRS_NO then @rk+1 else 1 end as rk,@dept:=a.AIRS_NO as dept,a.GROUND_DATE,a.AIRS_NO,a.ground_context from ground_job a,
(select @rk:=1,@dept:='' ) as b where a.model = '某36' and a.sj_num = 1 and LEFT(a.GROUND_DATE,7) = '2023-08'
             ORDER BY a.AIRS_NO,a.GROUND_DATE
)	t1 

解决方案：

3.根据（t1.GROUND_DATE-t1.rk）数据分组，并用COUNT(t1.GROUND_DATE-t1.rk) 查询相同的个数

select t1.AIRS_NO,t1.GROUND_DATE,t1.ground_context,t1.GROUND_DATE-t1.rk as dis,COUNT(t1.GROUND_DATE-t1.rk) as count from (
select @rk:=case when @dept=a.AIRS_NO then @rk+1 else 1 end as rk,@dept:=a.AIRS_NO as dept,a.GROUND_DATE,a.AIRS_NO,a.ground_context from ground_job a,
(select @rk:=1,@dept:='' ) as b where a.model = '某36' and a.sj_num = 1 and LEFT(a.GROUND_DATE,7) = '2023-08'
             ORDER BY a.AIRS_NO,a.GROUND_DATE
)	t1 GROUP BY t1.GROUND_DATE-t1.rk

4.用list集合过滤即可

List<GroundJobCountVo> collect = otherList.stream().filter(o -> o.getCount() >= 3).collect(Collectors.toList());