mysql查询：行转列与列转行

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提供函数、语法。