多合一查询

来源：互联网发布：南充程序员招聘编辑：程序博客网时间：2024/05/01 10:40

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