MySQL中的竖列变横列

最新推荐文章于 2024-08-13 15:38:53 发布

图南随笔

最新推荐文章于 2024-08-13 15:38:53 发布

阅读量3.1k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql java python

本文链接：https://blog.csdn.net/tunan666/article/details/117715990

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了如何使用SQL将数据库中的竖列转换为横列，提供了三种方法：with rollup、if条件判断和case语句。通过实例展示了在学生成绩表中的应用，最终得到相同的结果，包括姓名、语文、数学、英语的得分以及平均分和总分。

摘要由CSDN通过智能技术生成

工作中经常会用到竖列变横列的情况，下面通过一个实例来说一下实现竖列变横列的几种方式：

创建数据库表：

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

以上三种方法得到的竖列变横列的结果一致，如下：