1、需求
有如下表
需求是按照一定规则对id进行分组
需求规则：以ID从小到大依次分组。当累计num字段合计>=1000时就新成立一个组，剩下的单独成一组
按照需求分组该这样
id = 1 num = 1023已超过1000 自己成一组
id = 2,3,4,5,6 合计为 1174 所以id = 2,3,4,5,6 成为一组
id = 7,8,9,10 合计为1035 D成一组
id =11 只剩下它了，也成一组。
2、构造测试数据
drop table t;
create table t(id int,num int);
insert into t
values (1,1023),(2,310),(3,276),(4,349),(5,45),(6,194),(7,408),(8,164),(9,338),(10,125),(11,333);
select * from t;
3、SQL实现
MySQL用变量很好实现
实现代码如下：
select id,num,
@t1 := @t1 + num num_sum, -- @t1 用来做累计使用
@inc as flag, -- @inc 用来做分组标记使用
if(@t1 >=1000,@inc := @inc +1,@inc) , -- 当>=1000时 分组标记变化
if(@t1 >=1000,@t1 := 0,@t1) -- 当>=1000时 重新累计
from t
,(select @t1:=0,@inc :=0) as t2 -- MySQL 查询顺序from 第一执行。所以利用这个特性给@t1和inc赋初值
ORDER BY id;
执行结果如下：
如图已按需求完成分组。
总结一下此写法要点：
1、利用select 语句 From子句优化执行来初始化变量
2、利用SELECT 语句 从第一行依次往下执行的特性 用变量做累计
3、利用SELELCT 多字段时 是从左到或执行。来先累计达到条件清零后供下一行使用。
MySQL这种变量写法不太通用。再写一个通过公共表式递归来实现
语句如下：
with recursive cte1
as
(
select min(id) as minid from t
)
, cte2 as
(
select id,num from t
inner join cte1 on t.id = cte1.minid
)
,cte3 as
(
select id,num,case when num >1000 then 1 else 0 end as changf,num as num_sum,0 as flag from cte2
union all
select a.id,a.num
,case when a.num + b.num_sum >=1000 then 1 else 0 end as changf
,case when a.num + b.num_sum >=1000 and changf =1 then a.num else a.num + b.num_sum end as num_sum,
case when a.num + b.num_sum >=1000 and changf =1 then flag +1 else flag end as flag
from t a inner join cte3 b on b.id +1 = a.id
)
select * from cte3;
递归写法比较好理解，这里就不做解释了。如有不明白的可留言。
执行结果如下：
同样得到了需求相应的分组
4、总结
这种需求其实也该用后端代码实现。因为循环来说，后端代码使用起来太轻松了
但我很喜欢用SQL去做尝试。对于一个高要求的开发人员来说，一定要对比在数据库实现和在应用实现所付出的性能代价，可维护代价等等，只有你两者都有较好的掌握时，才会做好更优的选择
enjoy SQL enjoy Code
