PostgresSQL: запрос о студентах

Question

Совсем новичок в PostgreSQL. У меня есть две таблицы: таблица студентов:

CREATE TABLE students (
student_id BIGSERIAL NOT NULL PRIMARY KEY, 
student_firstName VARCHAR(50) NOT NULL, 
student_secondName VARCHAR(50) NOT NULL, 
student_email VARCHAR(100),
student_birthdate DATE NOT NULL,
student_phone VARCHAR(12) NOT NULL,
student_address TEXT, 
student_group BIGINT REFERENCES groups(group_id) ON DELETE CASCADE,
UNIQUE (student_email),
UNIQUE (student_phone)
);

И связанная с ней таблица оценок:

CREATE TABLE grades (
grade_id BIGSERIAL NOT NULL PRIMARY KEY, 
grade_student BIGINT REFERENCES students(student_id) ON DELETE CASCADE,
grade_subj BIGINT REFERENCES plan(subj_id)ON DELETE CASCADE,
grade SMALLINT NOT NULL
);

Мне нужно написать функции, которые выводил бы имена и фамилии студентов, у которых а) Все пятерки, б) Все пятерки и одна четверка, в)Есть долги. С пунктами а) и в) я справился через подсчёт в массивы, вроде бы всё работает.

а) Все пятерки

CREATE OR REPLACE FUNCTION public.show_greatstudents()
 RETURNS TABLE(first_name character varying, second_name character varying)
 LANGUAGE plpgsql
AS $function$DECLARE
bad INT[];
perfect INT[];
BEGIN
SELECT ARRAY_AGG(grade_student) into bad from grades 
WHERE
grade = 2
or grade = 0
or grade = 3
or grade = 4;
SELECT ARRAY_AGG(grade_student) into perfect from grades 
WHERE
grade = 5;
RETURN QUERY SELECT student_firstName,student_secondName FROM students
where NOT student_id = ANY(bad)
AND student_id = ANY(perfect);
END; $function$

в) Есть долги

CREATE OR REPLACE FUNCTION public.show_badstudents()
 RETURNS TABLE(first_name character varying, second_name character varying)
 LANGUAGE plpgsql
AS $function$DECLARE
bad INT[];
BEGIN
SELECT ARRAY_AGG(grade_student) into bad from grades 
WHERE
grade = 2
or grade = 0;
RETURN QUERY SELECT student_firstName,student_secondName FROM students
where student_id = ANY(bad);
END; $function$

А вот с пятерками и одной четверкой теряюсь, не понимаю как проконтролировать момент, чтобы четверка была именно одна. Может кто-то подскажет?

P.S. Знаю, что сделано костыльно) Возможно всё это можно просто уместить в один короткий запрос, но я не знаю как, буду очень благодарен, если поможете.

Хорошего вечера!

Answer 1

Все пятерки одним запросом:

SELECT student_firstName,student_secondName FROM students
 where student_id in(
   SELECT grade_student
     FROM grades
    GROUP BY grade_student
   HAVING min(grade)=5
 )

Собственно группирует все оценки до студента, убеждается, что минимальная оценка 5, следовательно 4 и ниже у него нет.

Пятерки и одна четверка:

SELECT student_firstName,student_secondName FROM students
 where student_id in(
   SELECT grade_student
     FROM grades
    GROUP BY grade_student
   HAVING min(grade)=4
      AND count(*)-1 = sum(case when grade=5 then 1 else 0 end)
 )

Убеждаемся, что минимальная оценка 4 и количество 5 на 1 меньше общего количества оценок

БЛОГ НА HUSL

PostgresSQL: запрос о студентах

Ответы (1 шт):