龙空技术网

MySQL中的竖列变横列

图南随笔 1722

前言:

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