建表语句
查询语句
结果:
[img]http://dl.iteye.com/upload/attachment/0067/8315/b68712ca-a75f-371e-8bb0-a46829f5d94d.png[/img]
/*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
结果:
[img]http://dl.iteye.com/upload/attachment/0067/8315/b68712ca-a75f-371e-8bb0-a46829f5d94d.png[/img]