时间戳

首页 > 解决方案 > 编写嵌套 SQL 查询

sql - 编写嵌套 SQL 查询

问题描述

我有 3 个表作为 StudentData，其中包括学生的数据、包含所有提供的科目数据的 Subjects 表和包含学生为每个科目获得的分数的 Marks。Marks 表通过 StudentId 映射到 StudentData 表，通过 SubjectId 映射到 Subjects 表

学生数据表

科目表

标记表

我想要做的是选择每个科目的最高分和学生的名字，如下所示

结果集

所以我写了一个 Oracle PL/SQL 查询如下，

select MAX(marks)
from
    (select Marks ,subjects.name as SJN ,studentdata.name
    from (studentdata inner Join marks On studentdata.studentid = marks.studentid)
    Inner Join subjects On subjects.subjectid = marks.subjectid)
where   SJN in (select name from subjects);

但它只给出一个结果。请帮助我开发一个查询以获得我的预期结果集。

标签： sqloracle

解决方案

像这样的东西？第 1 - 26 行代表样本数据（您无需输入）；您需要的查询从第 28 行开始。

SQL> with
  2  -- sample data
  3  studentdata (studentid, name, course) as
  4    (select 1, 'olivier', 'it'    from dual union all
  5     select 2, 'noah', 'business' from dual union all
  6     select 3, 'jack', 'business' from dual union all
  7     select 4, 'mason', 'it'      from dual union all
  8     select 5, 'julion', 'it'     from dual),
  9  subjects (subjectid, name) as
 10    (select 1, 'java'           from dual union all
 11     select 2, 'business stg'   from dual union all
 12     select 3, 'python'         from dual union all
 13     select 4, 'statistics'     from dual union all
 14     select 5, 'mgt accounting' from dual union all
 15     select 7, 'social studies' from dual union all
 16     select 8, 'ess english'    from dual),
 17  marks (id, studentid, subjectid, marks) as
 18    (select 1, 1, 1, 56 from dual union all
 19     select 2, 1, 2, 78 from dual union all
 20     select 3, 1, 7, 83 from dual union all
 21     select 4, 1, 3, 45 from dual union all
 22     select 5, 1, 5, 63 from dual union all
 23     --
 24     select 6, 2, 1, 99 from dual union all
 25     select 7, 3, 2, 10 from dual union all
 26     select 8, 4, 7, 83 from dual)
 27  --
 28  select b.name subject, s.name student, m.marks
 29  from marks m join subjects b on b.subjectid = m.subjectid
 30  join studentdata s on s.studentid = m.studentid
 31  where m.marks = (select max(m1.marks)
 32                   from marks m1
 33                   where m1.subjectid = m.subjectid
 34                  )
 35  order by b.name, s.name;

SUBJECT        STUDENT      MARKS
-------------- ------- ----------
business stg   olivier         78
java           noah            99
mgt accounting olivier         63
python         olivier         45
social studies mason           83
social studies olivier         83

6 rows selected.

SQL>

推荐阅读