多合一查询

 

create table student(num varchar(20),name varchar(20),subject varchar(10),score float)

 

insert  student select '001','姓名1','英语',78.00

insert  student select '001','姓名1','数学',88.00

insert  student select '001','姓名1','政治',98.00

insert  student select '002','姓名2','英语',58.00

insert  student select '002','姓名2','数学',48.00

insert  student select '003','姓名3','英语',38.00

insert  student select '003','姓名3','数学',28.00

 

drop table student

create table student(num varchar(20),name varchar(20),subject varchar(10),score float)

 

insert  student select '001','姓名1','英语',78.00

insert  student select '001','姓名1','数学',88.00

insert  student select '001','姓名1','政治',98.00

insert  student select '002','姓名2','英语',58.00

insert  student select '002','姓名2','数学',48.00

insert  student select '002','姓名2','政治',96.00

insert  student select '003','姓名3','英语',38.00

insert  student select '003','姓名3','数学',28.00

insert  student select '003','姓名3','政治',97.00

 

 

 

select distinct a1.num,a1.name,c2.subject,c2.score, c1.subject,c1.score, c3.subject,c3.score

from student a1 ,

        (select * from student b2 where b2.subject='数学') c1,

        (select * from student b1 where b1.subject='英语' ) c2,

        (select * from student b3 where b3.subject='政治' ) c3

 where a1.num=c1.num and a1.num=c2.num and a1.num=c3.num