MySQL实现行列转换
作者:小旭2021 发布时间:2024-01-18 20:56:17
标签:MySQL,行,列,转换
实际应用中,会遇到需要把表的某些行转换成列,或者把列转换成行的情况。比如一张表在数据库中是这样的:
图1
但是,需要的结果可能是这样:
图2
这个时候就得行列转换了。
1.行转列的几种方法
1.1 case... when ... then ... else ... end
select uname,uid, -- 正常查询的字段
sum(
case
when course ='英语' then score -- 需要转换的字段
else 0
end) '英语',
sum(
case
when course= '物理' then score
else 0
end) '物理',
sum(
case
when course='化学' then score
else 0
end) '化学'
from course
group by uid
另一种写法:
case course
when '化学' then score
else 0
end
另外若省略‘else 0‘,则没有该课程的同学的分数会填充为null; sum替换成max结果一样。
1.2 if (`字段名1`=‘字段值’,,)
select uname,uid,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学'
from course
group by uname
貌似比第一种方法简洁一些,所以下面的扩展是基于这种方法的~
以上两种转换方法结果相同,如图2。另外实际应用中还可能需要有总计的结果,如图3.
图3
total这一列简单,直接在之前的查询基础上加一个sum(score) 'total'即可;Total这一行则可以看成之前的查询不加group by而聚合成一行。因此可以看做是两个表组合到一起如图4和图5:
图4
图5
1.3 if (`字段名1`=‘字段值’,,) + union
把两个查询结果拼接到一起就是图3的样子了,代码如下:
select uid,uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uname
union
select 'Total',null,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
1.4 if (`字段名1`=‘字段值’,,) + IFNULL()+with rollup
这种方法效果同1.3,
select ifnull(uid,'Total') uid, uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uid
with ROLLUP
比1.3简洁一些,效率应该也高一点。with rollup和group by配套使用,会在已有的查询结果上再多出一行,对结果再聚合成一行,即图5的那一行,若不是数字类型,则返回最下面一行的数据,最后一行分组的字段会显示null,因此在配合ifnull()就可以了。
有瑕疵,想把它变成 null,有待完善。
2.列转行
列转行刚好和行转列情况相反,即:
数据库中存储的是这样
图2
而我们需要这样的结果
图1
没有在创建新表,暂且把这个视图当成新表吧
create view rtc as
select ifnull(uid,'Total') uid,uname,
sum(if(`course`='英语',score,0)) '英语',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化学',score,0)) '化学',
sum(score) 'total'
from course
group by uid
with ROLLUP
-- 下面是列转行代码
select uid,uname,'英语' course,英语 score from rtc where uid <>'Total' and 英语>0
union all select uid,uname,'物理' ,物理 from rtc where uid <>'Total' and 物理>0
union all select uid,uname,'化学' ,化学 from rtc where uid <>'Total' and 化学>0
来源:https://www.cnblogs.com/chenyablog/p/15322573.html
0
投稿
猜你喜欢
- 概述从今天开始我们将开启一段自然语言处理 (NLP) 的旅程. 自然语言处理可以让来处理, 理解, 以及运用人类的语言, 实现机器语言和人类
- PHP simplexml_load_file() 函数实例转换 XML 文件为 SimpleXMLElement 对象,然后输出对象的键和
- PHP如何获取当前页完整URL及其参数 <? echo 'http://'.$_SERVER[&
- 世上无难事,只要找到 Homebrew 的正确安装方式。Homebrew 是什么Homebrew是 mac的包管理器,仅需执行相应的命令,就
- Jupyter 是数据分析领域非常有名的开发环境,使用 Jupyter 写数据分析相关的代码会大大节约开发时间。设想这样一个场景:别的部门的
- 目前网络上有许多图片验证码形式,那些没有生成图片的验证码的抗破解防御能力简直不堪一击;有人直接在网页源码里显示出具体的数字,然后要求访问者输
- 有一个网站A想找别的网站作为代理商,在代理商的页面上插入自己的广告。它提供一个注册页面给代理商,代理商注册后,会得到一个ID,
- 今天用实验室的pycharm运行程序的时候发现出现了已安装的模块无法导入的情况,但实际上这个模块我已经在notebook中使用多次了,所以不
- 在python中安装了lxml-4.2.1,在使用时发现导入etree时IDE中报错Unresolved reference其实发现,不影响
- 视频本教程的视频碰撞是怎么回事在上一次教程中,我们添加了图形,将精灵从普通矩形更改为更漂亮的PNG图像。然而,这带来了一个问题:有时游戏会认
- 目录前置知识可迭代对象如何判断一个对象是否是可迭代对象?enumerate 函数多嵌套列表总结前置知识如果给定一个 list 或 tuple
- 本文实例讲述了Python带动态参数功能的sqlite工具类。分享给大家供大家参考,具体如下:最近在弄sqlite和python在网上参考各
- MySQL数据库配置技巧用root用户启动远程服务一直是安全大忌,因为如果服务程序出现问题,远程攻击者极有可能获得主机的完全控制权。MySQ
- 在这个擦亮自己的眼睛去看SQL Server的系列中的第二篇中提过要写历史渊源,这里的历史主要描述的是数据库本身的历史与SQL Server
- interfaceGo语言里面设计最精妙的应该算interface,它让面向对象,内容组织实现非常的方便,当你看完这一章,你就会被inter
- 很多人都将<数据库设计范式>作为数据库表结构设计“圣经”,认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证
- Series 的 sort_index(ascending=True) 方法可以对 index 进行排序操作,ascending 参数用于控
- 分享给大家一篇文章,教你怎样用Python画了一棵圣诞树,快来学习。如何用Python画一个圣诞树呢?最简单:height = 5stars
- 引子例如,一个人可能会在计算机上存储大量的照片、视频和文档文件,这些文件可能散落在不同的文件夹中,难以管理和查找。该程序可以根据文件类型将这
- 1、查找表结构,判断要加入的列是否已存在2、如果不存在,则执行添加 CREATE PROCEDURE `mysql_sp_add_