Mysql查询:行转列与列转行
mysql 查询 转行 转列
2023-09-14 09:14:06 时间
目录
1. 行转列
1.1 什么是行转列
从表面理解,就是表里存储在行上的数据,在查询结果里展示在列上面。举例说明一下,如果在数据库学生的科目分数是以下面的结构存储:
然后在输出查询结果的时候,却希望是这样的:
这就是行转列,把原本以行的结构存储的科目,在输出查询结果的时候在列上展示。
1.2 准备工作
#创建学生科目分数表
CREATE TABLE `subject_score` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`subject` varchar(100) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO subject_score (id, name, subject, score) VALUES(1, '张三', '语文', 96);
INSERT INTO subject_score (id, name, subject, score) VALUES(2, '张三', '数学', 89);
INSERT INTO subject_score (id, name, subject, score) VALUES(3, '张三', '英语', 72);
INSERT INTO subject_score (id, name, subject, score) VALUES(4, '李四', '语文', 76);
INSERT INTO subject_score (id, name, subject, score) VALUES(5, '李四', '数学', 84);
INSERT INTO subject_score (id, name, subject, score) VALUES(6, '李四', '英语', 98);
INSERT INTO subject_score (id, name, subject, score) VALUES(7, '王麻子', '语文', 19);
INSERT INTO subject_score (id, name, subject, score) VALUES(8, '王麻子', '数学', 96);
INSERT INTO subject_score (id, name, subject, score) VALUES(9, '王麻子', '英语', 86);
1.3 行转列的实现原理
1.使用case when,先把行上数据转化到列上
select
name,
case when subject = '语文' then score else 0 end 'chinese',
case when subject = '数学' then score else 0 end 'match',
case when subject = '英语' then score else 0 end 'english'
from
subject_score ss;
2.从执行结果看,每一行数据,有两列都是0,再使用max()函数取最大值,应该就能得到行转列后的效果了。
select
name,
max(case when subject = '语文' then score else 0 end ) 'chinese',
max(case when subject = '数学' then score else 0 end ) 'match',
max(case when subject = '英语' then score else 0 end ) 'english'
from
subject_score ss
group by
name ;
2. 列转行
2.1 什么是列转行
和行转列刚好相反,从表面理解,就是表里存储在列上的数据,在查询结果里展示在行上面。
实际存储结构:
输出效果:
2.2 准备工作
#创建学生科目分数表
CREATE TABLE `subject_score2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`chinese` varchar(100) DEFAULT NULL,
`english` varchar(100) DEFAULT NULL,
`math` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO subject_score2 (id, name, chinese, english, math) VALUES(1, '张三', '86', '99', '65');
INSERT INTO subject_score2 (id, name, chinese, english, math) VALUES(2, '李四', '98', '36', '75');
INSERT INTO subject_score2 (id, name, chinese, english, math) VALUES(3, '王麻子', '72', '85', '89');
2.3 列转行的实现原理
列转行的实现原理比较简单,就是把各列拆分成行,最后再各行使用union进行联合查询,就得到了最终想要的结果。
select
name,subject,score
from
(select name,'语文' as subject,chinese as score from subject_score2 s1
union
select name,'英语' as subject ,english as score from subject_score2 s2
union
select name,'数学' as subject ,math as score from subject_score2 s3 ) s
order by name asc ;
3. 总结
行转列、列转行,其实并不是很复杂,实际是运用了一些技巧灵活使用了mysql提供函数、语法。
相关文章
- Mysql授权允许远程访问解决Navicat for MySQL连接mysql提示客户端不支持服务器请求的身份验证协议;考虑升级MySQL客户端
- 【Mysql 学习】日期函数函数
- mysql-proxy读写分离笔记
- MySQL联合查询语法内联、左联、右联、全联
- 分享三:mysql跨库查询
- mysql 常用命令,连接数据库,查看建表语句,批量导入数据,批量更新数据,连接查询
- MySql常用命令总结[转]
- mysql查询字段时实现左右补零
- MySQL实现定时任务
- Mysql Explain详解
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- Atitit postgresql data type 数据类型与mysql对应表 数据库常用数据类型 PostgreMysql 整数intgreterInt 小数numericFL
- Python MySQL查询在哪里(where)
- mysql的delete truncate drop说明 mysql删除外键 mysql删除索引 mysql查询外键key_column_usage mysql查询索引tatistics
- Nginx+Php-fpm+MySQL+Redis源码编译安装指南
- 记一次mysql慢查询优化
- 操作mysql的命令
- mysql模糊查询
- MySQL(10)聚合查询、分组查询、分页查询
- Mysql入门技能树-子查询
- 【MySQL】mysql查询语句大总结_Unit04
- MySQL gtid半同步复制详解与主从搭建
- Mysql错误:ERROR: No query specified