Mysql行与列的多种转换(行转列,列转行,多列转一行,一行转多列)

更新时间：2023年08月24日 09:53:01 作者：雷神乐乐

在MySQL中,行转列和列转行都是非常有用的操作,本文就来介绍一下Mysql行与列的多种转换,主要包括行转列,列转行,多列转一行,一行转多列,具有一定的参考价值,感兴趣的可以了解一下

CREATE TABLE CJ
(
    Name    varchar(32),
    Subject varchar(32),
    Result  int(10)
);
# 插入数据
insert into cj
values ('张三', '语文', 80),
       ('张三', '数学', 90),
       ('张三', '物理', 85),
       ('李四', '语文', 85),
       ('李四', '数学', 92),
       ('李四', '物理', 82);

一、行转列

第一步，先将科目分类好：

SELECT Name,
       CASE WHEN Subject='语文' THEN Result ELSE 0 END AS 语文,
       CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 数学,
       CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 物理
FROM cj;

第二步：将上面的结果看做一张表，从表中找出每一个新字段的最大值，对Name进行分组

SELECT T.Name,
       MAX(T.语文) 语文,
       MAX(T.数学) 数学,
       MAX(T.物理) 物理
FROM (SELECT Name,
       CASE WHEN Subject='语文' THEN Result ELSE 0 END AS 语文,
       CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 数学,
       CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 物理
FROM cj) T
GROUP BY T.Name;

案例二：查询用户安装APP的情况

create table app
(
    id  int,
    app varchar(32)
);
insert into app(id, app)
VALUES (1, '微信'),
       (2, '快手'),
       (3, 'QQ'),
       (4, '抖音'),
       (5, '美团'),
       (6, '饿了么'),
       (7, '支付宝'),
       (8, '拼多多'),
       (9, '高德地图');
CREATE TABLE app_install
(
    uid int,
    app varchar(32)
);
insert into app_install(uid, app)
VALUES (1, '微信'),
       (1, '美团'),
       (2, '支付宝'),
       (2, '高德地图'),
       (3, '拼多多');
select uid,
       case when app = '微信' then 1 else 0 end as 'wx',
       case when app = '快手' then 1 else 0 end as 'ks',
       case when app = 'QQ' then 1 else 0 end as 'qq',
       case when app = '抖音' then 1 else 0 end as 'dy',
       case when app = '美团' then 1 else 0 end as 'mt',
       case when app = '饿了么' then 1 else 0 end as 'elm',
       case when app = '支付宝' then 1 else 0 end as 'zfb',
       case when app = '拼多多' then 1 else 0 end as 'pdd',
       case when app = '高德地图' then 1 else 0 end as 'gd'
from app_install;
select t.uid,
       case when max(t.wx) then '已安装' else '未安装' end as 'wx',
       case when max(t.ks) then '已安装' else '未安装' end as 'ks',
       case when max(t.qq) then '已安装' else '未安装' end as 'qq',
       case when max(t.dy) then '已安装' else '未安装' end as 'dy',
       case when max(t.mt) then '已安装' else '未安装' end as 'mt',
       case when max(t.elm) then '已安装' else '未安装' end as 'eml',
       case when max(t.zfb) then '已安装' else '未安装' end as 'zfb',
       case when max(t.pdd) then '已安装' else '未安装' end as 'pdd',
       case when max(t.gd) then '已安装' else '未安装' end as 'gd'
from  (select uid,
       case when app = '微信' then 1 else 0 end as 'wx',
       case when app = '快手' then 1 else 0 end as 'ks',
       case when app = 'QQ' then 1 else 0 end as 'qq',
       case when app = '抖音' then 1 else 0 end as 'dy',
       case when app = '美团' then 1 else 0 end as 'mt',
       case when app = '饿了么' then 1 else 0 end as 'elm',
       case when app = '支付宝' then 1 else 0 end as 'zfb',
       case when app = '拼多多' then 1 else 0 end as 'pdd',
       case when app = '高德地图' then 1 else 0 end as 'gd'
from app_install) t
group by t.uid;

连表比子查询要好

二、列转行

建表

CREATE TABLE CJ2
(
    Name varchar(32),
    `语文` int(10),
    `数学` int(10),
    `物理` int(10)
);
# 插入数据
insert into cj2 values ('张三',80,90,90),('李四',85,92,92);

原表：

SELECT Name,'语文' cource,语文 result
FROM cj2
union all
SELECT Name,'数学' cource,数学 result
FROM cj2
union all
SELECT Name,'物理' cource,物理 result
FROM cj2;
# 查询后按照结果排序
SELECT *
FROM (SELECT Name,'语文' cource,语文 result
FROM cj2
union all
SELECT Name,'数学' cource,数学 result
FROM cj2
union all
SELECT Name,'物理' cource,物理 result
FROM cj2) T
ORDER BY T.Name;

三、多列转一行

将科目与分数排在一列

SELECT Name,GROUP_CONCAT(Subject,':',Result) 成绩
FROM cj
group by Name;

四、一行转多列

将上表还原

# 建表
CREATE TABLE CJ3
(
    Name varchar(32),
    `成绩` varchar(50)
);
# 插入数据
insert into cj3
values ('张三', '语文:80,数学:90,物理:85'),
       ('李四', '语文:85,数学:92,物理:82');

SELECT Name,
       CASE
           WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1)
           else 0 end as 语文,
       CASE
           WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1)
           else 0 end as 数学,
       CASE
           WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1)
           else 0 end as 物理
from cj3;

SELECT T1.Name, '语文' Cource, T1.语文 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1)
                 else 0 end as 语文
      from cj3) T1
union all
SELECT T2.Name, '数学' Cource, T2.数学 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1)
                 else 0 end as 数学
      from cj3) T2
union all
SELECT T3.Name, '物理' Cource, T3.物理 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) T3;

SELECT *
FROM (SELECT T1.Name, '语文' Cource, T1.语文 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1)
                 else 0 end as 语文
      from cj3) T1
union all
SELECT T2.Name, '数学' Cource, T2.数学 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1)
                 else 0 end as 数学
      from cj3) T2
union all
SELECT T3.Name, '物理' Cource, T3.物理 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) T3) T
ORDER BY T.Name;

五、行转列的其他案例

准备一张result表

行转列

# 查询1000号学生四门科目的成绩
select StudentNo,
       case when SubjectNo = 1 then StudentResult else 0 end as 高等数学1,
       case when SubjectNo = 2 then StudentResult else 0 end as 高等数学2,
       case when SubjectNo = 3 then StudentResult else 0 end as java编程,
       case when SubjectNo = 4 then StudentResult else 0 end as hadoop理论
from result
where StudentNo = 1000;

# 简化
select StudentNo, MAX(高等数学1) math1, MAX(高等数学2) math2, MAX(java编程) java, MAX(hadoop理论) hadoop
from (select StudentNo,
             case when SubjectNo = 1 then StudentResult else 0 end as 高等数学1,
             case when SubjectNo = 2 then StudentResult else 0 end as 高等数学2,
             case when SubjectNo = 3 then StudentResult else 0 end as java编程,
             case when SubjectNo = 4 then StudentResult else 0 end as hadoop理论
      from result
      where StudentNo = 1000) T;

到此这篇关于Mysql行与列的多种转换(行转列,列转行,多列转一行,一行转多列)的文章就介绍到这了,更多相关Mysql行与列转换内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:

数据库实现行列转换(mysql示例)

mysql 行列动态转换的实现(列联表,交叉表)

MySQL5.1主从同步出现Relay log read failure错误解决方法

这篇文章主要介绍了MySQL5.1主从同步出现Relay log read failure错误解决方法,需要的朋友可以参考下

2014-07-07
mysql导入导出数据中文乱码解决方法小结

本文章总结了mysql导入导出数据中文乱码解决方法，出现中文乱码一般情况是导入导入时编码的设置问题，我们只要把编码调整一致即可解决此方法，下面是搜索到的一些方法总结,方便需要的朋友

2012-10-10
MySQL最左匹配原则详细分析

首先回顾一下什么是最左匹配(也有称之为最左前缀)？顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配

2022-12-12
MySQL权限变更何时生效

本文为大家讲述了对三种级别权限的变更后,使其生效的方法,有需要的朋友可以借鉴参考下,希望能够有所帮助,祝大家多多进步,早日升职加薪<BR>

2023-10-10
SQL中写入包含有英文单引号“ '''' ”失败问题深入详解

这篇文章主要介绍了SQL中写入包含有英文单引号“ ' ”失败问题深入详解,列举了具体实例讲解，有感兴趣的同学可以研究下

2021-03-03
wamp中mysql安装时能启动重启后无法启动的解决办法

这篇文章主要介绍了wamp中mysql安装时能启动重启后无法启动的解决办法 ,需要的朋友可以参考下

2018-08-08
一次Mysql死锁排查过程的全纪录

在测试环境测试给用户并发发送卡券时，出现了死锁，通过查找相关的资料解决了这个，所以想着总结出来，所以下面这篇文章主要是关于一次Mysql死锁排查过程的全纪录，需要的朋友可以参考下，希望大家从中能有所帮助。

2017-02-02
如何解决安装MySQL5.0后出现1607异常

小伙们在安装mysql5.0的时候是不是遇到过1607异常问题，大家都是怎么解决的呢？下面小编跟大家分享我是如何解决安装MySQL5.0后出现1607异常的，需要的朋友可以参考下

2015-10-10
mysql查询每小时数据和上小时数据的差值实现思路详解

这篇文章主要介绍了mysql查询每小时数据和上小时数据的差值,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

2020-04-04
Windows系统中完全卸载MySQL数据库实现重装mysql

这篇文章主要介绍了Windows系统中完全卸载MySQL数据库实现重装mysql数据库的方法，本文给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友参考下吧

2018-05-05