--student 学生表
create TABLE student_1(
sut_id char(8) primary key not null,
sut_name varchar(20)
)
--课程表course
create TABLE course_1(
course_id char(8) primary key not null,
course_name varchar(50)
)
--成绩表score
create TABLE score_1(
course_id char(8),
stu_id char(8),
score int ,
FOREIGN KEY (course_id) REFERENCES course_1(course_id),
FOREIGN KEY (stu_id) REFERENCES student_1(sut_id)
)
insert into student_1 ( sut_id ,sut_name) VALUES ( '01' ,'张某')
insert into student_1 ( sut_id ,sut_name) VALUES ( '02' ,'王某')
insert into student_1 ( sut_id ,sut_name) VALUES ( '03' ,'李某')
insert into student_1 ( sut_id ,sut_name) VALUES ( '04' ,'赵某')
insert into course_1 ( course_id ,course_name) VALUES ('001' ,'语文')
insert into course_1 ( course_id ,course_name) VALUES ('002' ,'数学')
insert into course_1 ( course_id ,course_name) VALUES ('003' ,'英语')
insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'01' ,80)
insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'02' ,50)
insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'03' ,60)
insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'04' ,50)
insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'01' ,70)
insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'02' ,50)
insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'03' ,60)
insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'04' ,80)
insert into score_1 (course_id,stu_id,score) VALUES ( '003' ,'01' ,70)
insert into score_1 (course_id,stu_id,score) VALUES ( '003' ,'02' ,40)
insert into score_1 (course_id,stu_id,score) VALUES ( '003' ,'03' ,90)
insert into score_1 (course_id,stu_id,score) VALUES ( '003' ,'04' ,80)
想要的结果是
--- 学生id 学生姓名 语文 数学 英语
-- 01 张某 80 70 70
-- 02 王某 50 50 40
-- .....
------解决方案--------------------
----student 学生表
--create TABLE student_1(
-- sut_id char(8) primary key not null,
-- sut_name varchar(20)
--)
----课程表course
--create TABLE course_1(
-- course_id char(8) primary key not null,
-- course_name varchar(50)
--)
----成绩表score
--create TABLE score_1(
-- course_id char(8),
-- stu_id char(8),
-- score int ,
-- FOREIGN KEY (course_id) REFERENCES course_1(course_id),
-- FOREIGN KEY (stu_id) REFERENCES student_1(sut_id)
--)
--insert into student_1 ( sut_id ,sut_name) VALUES ( '01' ,'张某')
--insert into student_1 ( sut_id ,sut_name) VALUES ( '02' ,'王某')
--insert into student_1 ( sut_id ,sut_name) VALUES ( '03' ,'李某')
--insert into student_1 ( sut_id ,sut_name) VALUES ( '04' ,'赵某')
--insert into course_1 ( course_id ,course_name) VALUES ('001' ,'语文')
--insert into course_1 ( course_id ,course_name) VALUES ('002' ,'数学')
--insert into course_1 ( course_id ,course_name) VALUES ('003' ,'英语')
--insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'01' ,80)
--insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'02' ,50)
--insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'03' ,60)
--insert into score_1 (course_id,stu_id,score) VALUES ( '001' ,'04' ,50)
--insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'01' ,70)
--insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'02' ,50)
--insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'03' ,60)
--insert into score_1 (course_id,stu_id,score) VALUES ( '002' ,'04' ,80)