mysql实现 行转列 列转行

1. 行转列

   建表语句

   CREATE TABLE `score` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `username` varchar(11) NOT NULL COMMENT '学生姓名',
     `subject` varchar(50) NOT NULL COMMENT '科目',
     `score` tinyint(3) unsigned NOT NULL COMMENT '成绩',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
   

   插入数据

   insert into score(`username`,`subject`,`score`) values('张三','语文','50'),('张三','数学','80'),('张三','英语','100'),('李四','语文','90'),('张三','数学','40'),('张三','英语','90')
   

   转换前结果
   
   转换后结果
   
   当前只介绍静态，列确定得方法，至于动态列的情况，下次再进行讲解，使用if，执行sql为：

   select 
   `username`,
   max(if(`subject`='语文',score,0)) as '语文',
   max(if(`subject`='数学',score,0)) as '数学',
   max(if(`subject`='英语',score,0)) as '英语'
   from score group by username;
   

2. 列转行
   建表语句

   CREATE TABLE `score1` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `username` varchar(255) NOT NULL,
     `cn` int(255) NOT NULL,
     `math` int(255) NOT NULL,
     `en` int(255) NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   

   插入数据

   insert into score1(`username`,`cn`,`math`,`en`) values('张三',90,80,100),('李四',60,40,70)
   

   转换前结果
   
   转换后结果
   
   执行sql为

   select `username`,'语文' as 'subject',`cn` as 'score' from score1
   union all 
   select `username`,'数学' as 'subject',`math` as 'score' from score1
   union all
   select `username`,'英语' as 'subject',`en` as 'score' from score1