Mysql查询：行转列与列转行

清疚 2024-03-31 09:04 93阅读 0赞

**目录**

1. 行转列

1.1 什么是行转列

1.2 准备工作

1.3 行转列的实现原理

2. 列转行

2.1 什么是列转行

2.2 准备工作

2.3 列转行的实现原理

3. 总结

--------------------

## 1. 行转列 ##

### 1.1 什么是行转列 ###

从表面理解，就是表里存储在行上的数据，在查询结果里展示在列上面。举例说明一下，如果在数据库学生的科目分数是以下面的结构存储：

![8a9186418f20421db672ed61ccfa1d57.png][]

然后在输出查询结果的时候，却希望是这样的：

![1b69c194a3f24b3cb54133e20b35f678.png][]

这就是行转列，把原本以行的结构存储的科目，在输出查询结果的时候在列上展示。

### 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;

![baa9f89d419e49398cfa3504f2d91877.png][]

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 ;

![e39bea8dfd7a4c79a5a21cfbbc395753.png][]

## 2. 列转行 ##

### 2.1 什么是列转行 ###

和行转列刚好相反，从表面理解，就是表里存储在列上的数据，在查询结果里展示在行上面。

实际存储结构：

![4921662bc0e44892b8582d8b08934df9.png][]

输出效果：

![ace9440120414ce59c4a67e826177afe.png][]

### 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 ;

![407d84806f2e48819ccd98af610d03b9.png][]

## 3. 总结 ##

行转列、列转行，其实并不是很复杂，实际是运用了一些技巧灵活使用了mysql提供函数、语法。

[8a9186418f20421db672ed61ccfa1d57.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/176574638d4d48b1894d3846addd55a9.png
[1b69c194a3f24b3cb54133e20b35f678.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d65b9acaf4664902baa591f2f9a15eb3.png
[baa9f89d419e49398cfa3504f2d91877.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/b9537a8a352a424d94d3983fce3a8c2b.png
[e39bea8dfd7a4c79a5a21cfbbc395753.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/cf2b4c73e972450c8e4a5b668f0a4835.png
[4921662bc0e44892b8582d8b08934df9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/13f5ae75c614476395ca798d30cb78f5.png
[ace9440120414ce59c4a67e826177afe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/be4f3c916bc64e2186b4c518e5faef59.png
[407d84806f2e48819ccd98af610d03b9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0295be6e31af48b0a013d04828392a64.png