Mam 3 tabele jako StudentData, które zawierają dane studentów, tabelę Subjects, która zawiera dane wszystkich oferowanych przedmiotów i oceny, która ma oceny uzyskane przez studentów z każdego przedmiotu.

studentdata table

Subjects table

Marks table

To, co chcę zrobić, to wybrać maksymalną liczbę ocen z każdego przedmiotu i nazwisko ucznia w następujący sposób

Result Set

Więc napisałem zapytanie Oracle PL / SQL w następujący sposób:

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);

Ale daje tylko jeden wynik. Proszę o pomoc w opracowaniu zapytania w celu uzyskania oczekiwanego zestawu wyników.

0
BenSV 2 kwiecień 2020, 09:56

3 odpowiedzi

Najlepsza odpowiedź

Coś takiego? Wiersze od 1 do 26 reprezentują przykładowe dane (nie wpisujesz tego); zapytanie, którego potrzebujesz, zaczyna się w linii # 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>
1
Littlefoot 2 kwiecień 2020, 07:08

Możesz użyć funkcji analitycznej ROW_NUMBER w następujący sposób:

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;
1
Popeye 2 kwiecień 2020, 07:10

Pierwsza rzecz, która przychodzi na myśl: wybierz najlepszą ocenę z danego przedmiotu, a następnie wybierz ucznia (uczniów) z tą oceną z tego przedmiotu:

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
);

Jak widzisz, wybieramy dwukrotnie z marks. Można tego uniknąć dzięki funkcji okna:

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;

Inną opcją jest dołączenie i sprawdzenie, czy nie ma lepszych ocen dla przedmiotu:

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
);

Nie potrafię powiedzieć, która z tych opcji jest najlepsza. Wybierz ten, który uważasz za najbardziej czytelny. Niezależnie od tego, które zapytanie wybierzesz, ten indeks powinien pomóc DBMS w szybkim znalezieniu najwyższych ocen:

create index idx on marks(subjectid, marks);
1
Thorsten Kettner 2 kwiecień 2020, 07:30