遇到一个关于MySQL查询上的问题，题目如下：我写的建表语句在代码文段中，方便解决，你能够直接入手解决问题，减掉那些不必要的麻烦，直接入手帮忙。
问题：最后第4题做了很久都没达到题目要求，其中我的不完整查询语句也在其中，希望各位能帮忙解决一下，解决成功的必定采纳
–1.学生表
students_test(stu_id,stu_name,stu_birth,stu_sex) –学生编号（主键，自增）,学生姓名, 出生年月,性别
–2.课程表
subjects_test(subj_id,subj_name,tea_id) –课程编号（主键，自增）, 课程名称, 教师编号（外键）
–3.教师表
teachers_test(tea_id,tea_name) –教师编号（主键，自增）,教师姓名
–4.成绩表
scores_test(stu_id,subj_id,sco_result) –学生编号,课程编号,分数
插入数据
--插入学生表测试数据
'1','刘一' , '2000-01-01' , '男';
'2','陈二' , '1999-10-01' , '女';
'3','张三' , '2001-11-11' , '男';
'4','李四' , '2002-09-08' , '女';
'5','王五' , '1998-12-01' , '男';
'6','赵六' , '1996-10-01' , '女';
'7','孙七' , '1995-05-01' , '男';
'8','周八' , '2003-08-01' , '女';
'9','吴九' , '2002-09-10' , '男';
'10','郑十' , '2001-02-02' , '女';
--课程表测试数据
'1','JAVA' , '3';
'2','MYSQL' , '1';
'3','HTML' , '2';
--教师表测试数据
'1' , '朱老';
'2' , '万老';
'3' , '王老';
--成绩表测试数据
'1', '1', 70;
'1', '2', 80;
'1', '3', 89;
'2', '1', 90;
'2', '2', 50;
'3', '1', 80;
'3', '2', 70;
'4', '1', 90;
'4', '2', 30;
'4', '3', 40;
'5', '1', 76;
'5', '2', 87;
'5', '3', 88;
'6', '1', 41;
'6', '3', 54;
'7', '2', 79;
'7', '3', 68;
'8', '1', 91;
'8', '3', 74;
'9', '2', 81;
'9', '3', 92;
'10', '2', 55;
编写sql查询语句：
1）查询朱老所教课程，且分数低于70的学生姓名和分数
2）查询平均成绩不及格的同学的学生编号和学生姓名和平均成绩
3）查询学生信息的学生编号、学生姓名、选课总数、所有课程的总成绩
4）查询所有学生的所有课程的成绩以及平均成绩，按照平均成绩降序排序
要求：ID JAVA MYSQL HTML AVG_SCORE
# -1.学生表
# students_test(stu_id,stu_name,stu_birth,stu_sex) –学生编号（主键，自增）,学生姓名, 出生年月,性别
drop table if exists students_test;
create table if not exists students_test(
stu_id int primary key auto_increment comment '学生编号',
stu_name varchar(50) not null comment '学生姓名',
stu_birth date comment '出生年月',
stu_sex varchar(1) not null comment '性别'
)engine = innodb default charset = utf8 comment '学生表';
# 2.课程表
# subjects_test(subj_id,subj_name,tea_id) –课程编号（主键，自增）, 课程名称, 教师编号（外键）
drop table if exists subjects_test;
create table if not exists subjects_test(
subj_id int primary key auto_increment comment '课程编号',
subj_name varchar(50) not null comment '课程名称',
tea_id int comment '教师编号',
constraint fk_tea_id foreign key (tea_id) references teachers_test(tea_id)
)engine = innodb default charset = utf8 comment '课程表';
# 3.教师表
# teachers_test(tea_id,tea_name) –教师编号（主键，自增）,教师姓名
drop table if exists teachers_test;
create table if not exists teachers_test(
tea_id int primary key auto_increment comment '教师编号',
tea_name varchar(50) comment '教师姓名'
)engine = innodb default charset = utf8 comment '教师表';
# 4.成绩表
# scores_test(stu_id,subj_id,sco_result) –学生编号,课程编号,分数
drop table if exists scores_test;
create table if not exists scores_test(
stu_id int not null comment '学生编号',
subj_id int not null comment '课程编号',
sco_result int not null comment '分数'
)engine = innodb default charset = utf8 comment '成绩表';
# 插入数据
# 插入学生表测试数据
insert into students_test values ('1','刘一' , '2000-01-01' , '男'),('2','陈二' , '1999-10-01' , '女'),('3','张三' , '2001-11-11' , '男'),('4','李四' , '2002-09-08' , '女'),('5','王五' , '1998-12-01' , '男'),('6','赵六' , '1996-10-01' , '女'),('7','孙七' , '1995-05-01' , '男'),('8','周八' , '2003-08-01' , '女'),('9','吴九' , '2002-09-10' , '男'),('10','郑十' , '2001-02-02' , '女');
# 查看学生表数据
select * from students_test;
# 插入课程表测试数据
insert into subjects_test values ('1','JAVA','3'),('2','MYSQL','1'),('3','HTML','2');
# 查看课程表
select * from subjects_test;
# 插入教师表数据
insert into teachers_test values ('1','朱老师'),('2','万老师'),('3','王老师');
# 查看教师表
select * from teachers_test;
# 插入成绩表数据
insert into scores_test values ('1','1',70),('1','2',80),('1','3',89),('2','1',90),('2','2',50),('3','1',80),('3','2',70),('4','1',90),('4','2',30),('4','3',40),('5','1',76),('5','2',87),('5','3',88),('6','1',41),('6','3',54),('7','2',79),('7','3',68),('8','1',91),('8','3',74),('9','2',81),('9','3',92),('10','2',55);
# 查看成绩表
select * from scores_test;
# 编写sql查询语句：
# 4）查询所有学生的所有课程的成绩以及平均成绩，按照平均成绩降序排序
# 要求：ID JAVA MYSQL HTML AVG_SCORE
# 这一段的代码的问题是有的学生没部分科目成绩，但是被覆盖上上一位的成绩了
select sc1.stu_id 'ID',
ja.cj 'JAVA',
mysql.cj 'MYSQL',
html.cj 'HTML'
from scores_test sc1,subjects_test su1,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 1 and sc2.subj_id = su2.subj_id) ja,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 2 and sc2.subj_id = su2.subj_id) mysql,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 3 and sc2.subj_id = su2.subj_id) html
where sc1.subj_id = su1.subj_id or ja.id = sc1.stu_id or mysql.id = sc1.stu_id or html.id = sc1.stu_id group by sc1.stu_id;
# 这一段的问题是，加上平均成绩后，上一段代码的其他内容都被一名学生的成绩覆盖了，只有平均成绩是对的
select sc1.stu_id 'ID',
ja.cj 'JAVA',
mysql.cj 'MYSQL',
html.cj 'HTML',
pj.pjcj 'AVG_SCORE'
from scores_test sc1,subjects_test su1,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 1 and sc2.subj_id = su2.subj_id) ja,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 2 and sc2.subj_id = su2.subj_id) mysql,
(select stu_id id,sco_result cj from scores_test sc2,subjects_test su2 where su2.subj_id = 3 and sc2.subj_id = su2.subj_id) html,
(select stu_id id,avg(sco_result) pjcj from scores_test sc2 group by sc2.stu_id) pj
where sc1.subj_id = su1.subj_id or ja.id = sc1.stu_id or mysql.id = sc1.stu_id or html.id = sc1.stu_id or pj.id = sc1.stu_id or ja.id = mysql.id or mysql.id = html.id or html.id = pj.id group by pj.pjcj desc;
