@TOCoracle复杂查询

oracle 班级学生科目多表复杂查询

数据表结构

1. 学生表（student）
   字段（stuid 学生学号，stuName 学生姓名， age 年龄， sex性别， phone 联系电话, email 邮箱， address 住址, classId 班级编号）

2. 班级表（class）
   字段（classId 班级编号, className 班级名称, blongTeacherid 班主任id ）

3. 科目表（subject）
   字段（subId 科目编号， subName 科目名称）

4. 教师表（teacher）
   字段（teacherId 教师编号， t_name 教师名称， age 年龄， t_age 教龄 ）

5. 分数表（score）
   字段（scoreId 分数Id, stuId 学生学号， subId 科目编号， scoreNumber 分数）

6. 教师科目表（subjectTeacher）
   字段（subte 表id，subjectId 科目Id,teacherId 教师编号）

1.查询科目1高于平均分的学生信息

 select student.*, scorenumber, subid
   from student, score
  where student.stuid = score.stuid
    and subId = '100'
    and scorenumber >
        (select avg(scoreNumber) avgScore from score where subId = '100');

或者

 select student.*, score1.scorenumber, score1.subid, avgScore
  from student,
       score score1,
       (select avg(scoreNumber) avgScore
          from score 
      where subId = '100') score2
 where student.stuid = score1.stuid
   and score1.subId = '100'
   and score1.scorenumber > score2.avgScore;

运行结果

2.查询所有科目都在所在科目平均分以上的学生信息

select student.*
  from student,
       (select count(*), stuid
          from score score1,
               (select avg(scoreNumber) avgScore, subId
                  from score
                 group by subId) score2
         where score1.subid = score2.subid
           and score1.scorenumber > score2.avgscore
         group by stuid
        having count(*) >= (select count(*) from subject)
         order by score1.stuid) a
 where student.stuid = a.stuid;

运行结果

3.查询总成绩最高的学生信息和最低的学生信息以及总成绩

解法1：分别查出成绩最高的学生信息和成绩最低的学生信息之后用union求并集

select student.*, b.maxscore
  from student,
       (select sum(scoreNumber) sumScore, stuid
          from score
         group by stuid
         order by sumScore desc) a,
       (select max(sumscore) maxscore
          from (select sum(scoreNumber) sumScore, stuid
                  from score
                 group by stuid
                 order by sumScore desc) a) b
 where student.stuid = a.stuid
   and a.sumscore = b.maxscore
union
select student.*, b.minscore
  from student,
       (select sum(scoreNumber) sumScore, stuid
          from score
         group by stuid
         order by sumScore desc) a,
       (select min(sumscore) minscore
          from (select sum(scoreNumber) sumScore, stuid
                  from score
                 group by stuid
                 order by sumScore desc) a) b
 where student.stuid = a.stuid
   and a.sumscore = b.minscore;

解法2：先在score分数表中按stuid分组查出每个学生的总成绩，按总成绩排序之后用rownum取出第一条和最后一条即最高分和最低分，再连接student表查询学生信息以及总分数

 select *
  from student,
       (select *
          from (select sum(scoreNumber) sumScore, stuid
                  from score
                 group by stuid
                 order by sumScore desc) a
         where rownum = 1
        union
        select *
          from (select sum(scoreNumber) sumScore, stuid
                  from score
                 group by stuid
                 order by sumScore) a
         where rownum = 1) b
 where student.stuid = b.stuid;

运行结果

4.查各科成绩最高的学生信息

select student.*, subject.subName, maxScoreNumber
  from student,subject,
       score,
       (select max(scorenumber) maxScoreNumber, subid
          from score
         group by subid) a
 where student.stuid = score.stuid
   and score.subid = a.subid
   and score.subid=subject.subid
   and score.scorenumber = a.maxScoreNumber;

运行结果

5.查询各科成绩都是优秀的学生信息（分数大于80）

解法1：将成绩按照stuid分组统计分数大于80分的数量，若等于所有科数目则各科成绩都优秀
select student.*
  from student,
       (select stuid, count(*) countNum
          from (select score.* from score where scoreNumber > =80) a
         group by stuid) b
 where student.stuid = b.stuid
   and b.countNum >= 3;
解法2：将成绩按照stuid分组求成绩最小值，若一个学生最低成绩>=80,则该学生各科成绩都优秀
select stu.*
  from (select min(sc.scorenumber) minscore, sc.stuid
          from score sc
         group by sc.stuid
        having min(sc.scorenumber) >= 80) stuscore
  left join student stu
    on stuscore.stuid = stu.stuid;  

运行结果

6.查询各班各科平均成绩

select classid, subName, avg(scoreNumber)
  from student, score, subject
 where student.stuid = score.stuid
   and score.subid = subject.subid
 group by classid, subName
 order by classid;

运行结果

7.查询各班最高分学生的信息

select student.*, b.maxSumScore
  from student,
       (select classid, max(sumScore) maxSumScore
          from (select classid, score.stuid, sum(scoreNumber) sumScore
                  from student, score
                 where student.stuid = score.stuid
                 group by classid, score.stuid) a
         group by classid) b,
       (select classid, score.stuid, sum(scoreNumber) sumScore
          from student, score
         where student.stuid = score.stuid
         group by classid, score.stuid) a
 where student.stuid = a.stuid
   and a.sumScore = b.maxSumScore
   and a.classid = b.classid;

运行结果

8.查询所有学生各科成绩按照总成绩降序排列

SELECT stu.stuid,
       stu.stuname,
       a.java,
       b.sql,
       c.html,
       a.java + b.sql + c.html sumscore
  FROM student stu,
       (SELECT s.scorenumber java, s.stuid FROM score s WHERE s.subid = 100) a,
       (SELECT s.scorenumber sql, s.stuid FROM score s WHERE s.subid = 200) b,
       (SELECT s.scorenumber html, s.stuid FROM score s WHERE s.subid = 300) c
 WHERE stu.stuid = a.stuid
   AND stu.stuid = b.stuid
   AND stu.stuid = c.stuid
 order by sumscore desc

运行结果

9.查询韩梅梅老师所带课程的成绩

select stuname, t_name, score.subid, scoreNumber
  from subjectteacher, teacher, score, student
 where subjectteacher.teacherid = teacher.teacherid
   and score.subid = subjectteacher.subjectid
   and student.stuid = score.stuid
   and t_name = '韩梅梅';

运行结果

10.查询各班分数在前3位的学生姓名信息以及各科成绩和总分数，并按照总分数降序排列

select student.*, score.scoreNumber, sumScore
    from student,
         score,
         (select stuid, sum(scoreNumber) sumScore
            from score
           group by stuid
           order by sumScore desc) a
   where student.stuid = a.stuid
     and student.stuid = score.stuid
     and student.classid = 1001
     and rownum < 10
  union
  select student.*, score.scoreNumber, sumScore
    from student,
         score,
         (select stuid, sum(scoreNumber) sumScore
            from score
           group by stuid
           order by sumScore desc) a
   where student.stuid = a.stuid
     and student.stuid = score.stuid
     and student.classid = 1002
     and rownum < 10

运行结果

11.查询各个班级的科目一的优秀率

select a.classid, countNum1 / countNum
  from (select classid, count(*) countNum
          from student, score
         where student.stuid = score.stuid
           and subid = 100
         group by classid) a,
   
       (select classid, count(*) countNum1
          from student, score
         where student.stuid = score.stuid
           and subid = 100
           and score.scoreNumber >= 80
         group by classid) b
 where a.classid = b.classid;

运行结果

12.查询各个班级总成绩的优秀率

select a1.classid, countNum1 / countNum
  from (select classid, count(*) countNum
          from (select classid, student.stuid, sum(ScoreNumber)
                  from student, score
                 where student.stuid = score.stuid
                 group by classid, student.stuid
                 order by classid) a
         group by classid) a1,
       (select classid, count(*) countNum1
          from (select classid, student.stuid, sum(ScoreNumber)
                  from student, score
                 where student.stuid = score.stuid
                 group by classid, student.stuid
                having sum(scorenumber) > 240
                 order by classid) b
         group by classid) b1
 where a1.classid = b1.classid;

运行结果