Mysql怎么将将分组聚合的查询结果写入另一个表
课程设计
1
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 -
使用嵌套子查询即可：
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;
发表回复