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

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

通用方法一般是用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，否则也不用写得这么麻烦

使用嵌套子查询即可：
select time,
(select sum(number) from table where fruit='apple') apple,
(select sum(number) from table where fruit='pear') pear,
(select sum(number)er from table where fruit='peach') peach,
(select sum(number) from table where fruit='orange') orange,
(select sum(number) from table) total
from table group by time;

手动或写程序吧