Mysql怎么将将分组聚合的查询结果写入另一个表

Question

Mysql中怎么把分组聚合的查询结果(左边)插入到新表中实现下图这样的效果(右边)？

Answer 1

通用方法一般是用case when 来处理行列转换的问题

select time,
sum(case when fruit='apple' then number else 0 end ) apple,
sum(case when fruit='pear' then number else 0 end ) pear,
sum(case when fruit='peach' then number else 0 end ) peach,
sum(case when fruit='orange' then number else 0 end ) orange,
sum(number) total
from 表
group by time

可惜mysql不支持pivot，否则也不用写得这么麻烦