前言:
而今看官们对“mysql纵表转横表函数”大体比较关注,看官们都需要分析一些“mysql纵表转横表函数”的相关文章。那么小编也在网摘上搜集了一些有关“mysql纵表转横表函数””的相关文章,希望大家能喜欢,大家快快来学习一下吧!工作中经常会用到竖列变横列的情况,下面通过一个实例来说一下实现竖列变横列的几种方式:
创建数据库表:
CREATE TABLE `student_score` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_name` varchar(32) NOT NULL DEFAULT '姓名', `subject` varchar(32) NOT NULL COMMENT '科目', `score` tinyint(1) NOT NULL DEFAULT '0' COMMENT '分数', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';
添加实例数据后如下:
下面使用三种方式实现竖列变横列:
1、with rollup
select ifnull(user_name, 'TOTAL') as 姓名, sum(if(subject='语文', score, 0)) as 语文, sum(if(subject='数学', score, 0)) as 数学, sum(if(subject='英语', score, 0)) as 英语, round(sum(score)/3, 2) as 平均分, sum(score) as 总分 from student_score group by user_name with rollup
2、使用if
select user_name as 姓名, sum(if(subject='语文', score, 0)) as 语文, sum(if(subject='数学', score, 0)) as 数学, sum(if(subject='英语', score, 0)) as 英语, round(avg(score), 2) as 平均分, sum(score) as 总分 from student_score group by user_name union select user_name as 姓名, sum(chinese) as '语文', sum(math) as '数学', sum(english) as '英语', round(avg(score), 2) as 平均分, sum(score) as 总分 from( select 'TOTAL' as user_name, sum(if(subject='语文', score, 0)) as chinese, sum(if(subject='数学', score, 0)) as math, sum(if(subject='英语', score, 0)) as english, sum(score) as score from student_score group by subject ) t group by user_name
3、使用case
select user_name as 姓名, sum(case when subject = '语文' then score end) as 语文, sum(case when subject = '数学' then score end) as 数学, sum(case when subject = '英语' then score end) as 英语, round(avg(score), 2) as 平均分, sum(score) as 总分 from student_score group by user_name UNION ALL select user_name as 姓名, sum(chinese) as '语文', sum(math) as '数学', sum(english) as '英语', round(avg(score), 2) as '平均分', sum(score) as '总分' from ( select 'TOTAL' as user_name, sum(case when subject = '语文' then score end) as chinese, sum(case when subject = '数学' then score end) as math, sum(case when subject = '英语' then score end) as english, sum(score) as score from student_score group by subject ) t group by user_name
以上三种方法得到的竖列变横列的结果一致,如下:
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #mysql纵表转横表函数