有意思的SQL(四)

1、需求

有如下表

需求是按照一定规则对id进行分组
需求规则：以ID从小到大依次分组。当累计num字段合计>=1000时就新成立一个组，剩下的单独成一组

按照需求分组该这样

id = 1 num = 1023已超过1000 自己成一组
id = 2,3,4,5,6 num列累计合计为 1174 所以id = 2,3,4,5,6 成为一组
id = 7,8,9,10 num列累计合计为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