前言：
而今看官们对“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纵表转横表函数
