编写嵌套的SQL查询

Question

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

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

所以我写了一个甲骨文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);

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

Answer 1

首先想到的是：选择每个科目的最佳分数，然后选择该科目中具有该分数的学生：

select s.name as subject, m.marks, sd.name as studentname
from marks m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where (m.subjectid, m.marks) in
(
  select subjectid, max(marks)
  from marks
  group by subjetid
);

如您所见，我们从标记中选择两次。这可以通过窗口函数来避免：

select s.name as subject, m.marks, sd.name as studentname
from
(
  select
    subjectid,
    marks,
    max(marks) over (partition by subjectid) as max_marks
  from marks
) m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where m.marks = m.max_marks;

另一个选择是加入并检查主题是否没有更好的标记：

select s.name as subject, m.marks, sd.name as studentname
from marks m
join studentdata sd on sd.studentid = m.studentid
join subjects s on s.subjectid = m.subjectid
where not exists
(
  select null
  from marks m2
  where m2.subjectid = m.subjectid
  and m2.marks > m.marks
);

这些选项中哪一个是最好的，我不知道。选择您认为最易读的那个。无论您选择哪种查询，此索引都应有助于 DBMS 快速找到最高分：

create index idx on marks(subjectid, marks);

Answer 2

您可以按如下方式使用分析函数ROW_NUMBER：

SELECT SJN, MARKS, STUNAME FROM
(SELECT
    MARKS.MARKS,
    SUBJECTS.NAME   AS SJN,
    STUDENTDATA.NAME AS STUNAME,
    ROW_NUMBER() OVER (PARTITION BY SUBJECTS.SUBJECTID 
                          ORDER BY MARKS.MARKS DESC NULLS LAST) AS RN
FROM
    STUDENTDATA
    INNER JOIN MARKS ON STUDENTDATA.STUDENTID = MARKS.STUDENTID 
    INNER JOIN SUBJECTS ON SUBJECTS.SUBJECTID = MARKS.SUBJECTID)
WHERE RN = 1;

Answer 3

类似这样的？第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>