用单条SQL优化流程化的统计过程

最近是一次成功优化的体验，把原来流程化的Java+N条SQL的统计流程精简成了一条SQL语句，成功减少了很多行Java代码，减少很多次连接以及流量，效率提高了好几倍。


类似环境:

create table items (id int,price int, mnt int, total int);

insert into items values
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
(rand()*1000, rand()*100, rand()*10,0),
....
;


需求：

需要在id是1~100， 100~200，200~300...的商品分组上计算出销售总额，并且把这个总额记录到每个统计范围内最小id的那个商品的total栏目里面，同时该组其他id的total值置为0；


按一般流程的做法:

先select id/100 as level, sum(price*mnt) as total group by round(id/100)，得到一个结果集
然后得每个level所对应的min(id)
最后再一条条update所有的items, 非min(id)的total设置成0，min(id)设置成统计出来的total值.


优化后的SQL:


update items as i inner join (

select min(id) as id, floor(id/100) as level, sum(price*mnt) as total from items group by level

) as t on t.level=floor(i.id/100)

set i.total=if(i.id=t.id,t.total,0);

其实主要用到了update的过程中，join其他的表进行定位判断。