sql 行列转换

建表语句

/*Table structure for table `score` */

drop table if exists `score`;

CREATE TABLE `score` (
  `id` int(10) NOT NULL auto_increment,
  `score` int(3) default NULL,
  `subject` int(10) default NULL,
  `student` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `score` */

insert into `score` values (1,100,1,1),(2,99,2,1),(3,99,1,2),(4,98,2,2),(5,99,3,2);

/*Table structure for table `student` */

drop table if exists `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `student` */

insert into `student` values (1,'zhangsan'),(2,'lisi');

/*Table structure for table `subject` */

drop table if exists `subject`;

CREATE TABLE `subject` (
  `id` int(10) NOT NULL auto_increment,
  `subject` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `subject` */

insert into `subject` values (1,'chinese'),(2,'math'),(3,'english');

查询语句

select t.* ,(t.chinese + t.math+ t.english) as total from (select stu.name,max(case  sub.subject when  'chinese' then s.score else 0 end) as chinese,max(case  sub.subject when  'math' then s.score else 0 end) as math,max(case  sub.subject when  'english' then s.score else 0 end) as english  from student stu,score s,subject sub where stu.id = s.student and sub.id = s.subject group by name) t

结果：