Вывести отсортированный список завершённых приёмов

80. Вывести отсортированный список завершённых приёмов

Условие задачи:
Даны таблицы врачей, специализаций, пациентов и приёмов.

Необходимо вывести список только завершённых приёмов со следующими данными:

ФИО врача;
ФИО пациента;
дата приёма.

Результат необходимо отсортировать по дате приёма.

Код:

CREATE TABLE doctors (
    id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL
);

CREATE TABLE specializations (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE doctor_specializations (
    doctor_id INT REFERENCES doctors(id),
    specialization_id INT REFERENCES specializations(id),
    PRIMARY KEY (doctor_id, specialization_id)
);

CREATE TABLE patients (
    id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    birth_date DATE
);

CREATE TABLE appointments (
    id INT PRIMARY KEY,
    doctor_id INT REFERENCES doctors(id),
    patient_id INT REFERENCES patients(id),
    appointment_date DATE NOT NULL,
    status VARCHAR(20)
        CHECK (status IN ('scheduled', 'completed', 'canceled'))
);

INSERT INTO doctors VALUES
(1, 'Иванов А.П.'),
(2, 'Петрова М.С.'),
(3, 'Сидоров К.В.'),
(4, 'Кузнецова Е.И.');

INSERT INTO specializations VALUES
(1, 'Терапевт'),
(2, 'Кардиолог'),
(3, 'Хирург'),
(4, 'Невролог');

INSERT INTO doctor_specializations VALUES
(1, 1),
(2, 1),
(3, 3),
(4, 4);

INSERT INTO patients VALUES
(1, 'Волков А.', '1990-03-15'),
(2, 'Зайцева О.', '1985-07-22'),
(3, 'Лисицын П.', '2000-01-10'),
(4, 'Медведева А.', '1978-11-30'),
(5, 'Орлов Д.', '1995-06-05');

INSERT INTO appointments VALUES
(1, 1, 1, '2025-01-15', 'completed'),
(2, 1, 2, '2025-01-15', 'completed'),
(3, 1, 3, '2025-01-16', 'completed'),
(4, 2, 1, '2025-01-16', 'completed'),
(5, 2, 4, '2025-01-17', 'canceled'),
(6, 3, 5, '2025-01-17', 'completed'),
(7, 3, 2, '2025-01-18', 'scheduled'),
(8, 1, 4, '2025-01-18', 'completed'),
(9, 4, 3, '2025-01-19', 'scheduled'),
(10, 1, 5, '2025-01-19', 'completed');

Ожидаемый результат:

Иванов А.П.   | Волков И.    | 2025-01-15
Иванов А.П.   | Зайцева О.   | 2025-01-15
Иванов А.П.   | Лисицын П.   | 2025-01-16
Петрова М.С.  | Волков И.    | 2025-01-16
Сидоров К.В.  | Орлов Д.     | 2025-01-17
Иванов А.П.   | Медведева А. | 2025-01-18
Иванов А.П.   | Орлов Д.     | 2025-01-19

Спойлеры к решению

Подсказки

💡 Таблица appointments содержит идентификаторы врача и пациента, поэтому необходимо выполнить JOIN с таблицами doctors и patients.
💡 Через WHERE оставь только записи со статусом completed.
💡 Для сортировки по дате приёма используй ORDER BY appointment_date.
💡 Для стабильного порядка при одинаковой дате можно дополнительно сортировать по appointments.id.

Решение

SELECT
    d.full_name AS doctor_name,
    p.full_name AS patient_name,
    a.appointment_date
FROM appointments a
JOIN doctors d
    ON d.id = a.doctor_id
JOIN patients p
    ON p.id = a.patient_id
WHERE a.status = 'completed'
ORDER BY a.appointment_date, a.id;

Сначала:

JOIN doctors d
    ON d.id = a.doctor_id

добавляет к каждому приёму данные врача.

Затем:

JOIN patients p
    ON p.id = a.patient_id

добавляет данные пациента.

Условие:

WHERE a.status = 'completed'

оставляет только завершённые приёмы.

Записи со статусами:

scheduled
canceled

в результат не попадут.

Сортировка:

ORDER BY a.appointment_date, a.id

сначала располагает приёмы по возрастанию даты, а для приёмов с одинаковой датой сохраняет однозначный порядок по id.

Для приведённых данных результат запроса будет:

Иванов А.П.   | Волков А.    | 2025-01-15
Иванов А.П.   | Зайцева О.   | 2025-01-15
Иванов А.П.   | Лисицын П.   | 2025-01-16
Петрова М.С.  | Волков А.    | 2025-01-16
Сидоров К.В.  | Орлов Д.     | 2025-01-17
Иванов А.П.   | Медведева А. | 2025-01-18
Иванов А.П.   | Орлов Д.     | 2025-01-19

В исходных данных пациент с id = 1 записан как Волков А., хотя в указанном ожидаемом результате используется Волков И.. SQL-запрос вернёт значение, которое фактически хранится в таблице patients, то есть Волков А..