Некорректная выборка в SQL запросе

Question

Что имеем:

СУБД PostgreSQL 12

Есть три таблицы

worker - работники
id | name

Что нужно:

На конкретный день получать список работников в котором:

В начале списка будут те, кто выходят на работу в промежутке с time_from по time_to по основному графику не имея исключением выходной в этот день:
work.is_weekend = False AND (work_except.is_weekend = False OR work_except.is_weekend IS NULL)
Далее по списку те, у кого по расписанию был выходной, но они в качестве исключения выходят на работу:
work.is_weekend = True AND work_except.is_weekend = False
Далее по списку те, у кого выходной или те, кто его взяли. По сути все остальные:
work.is_weekend = True OR work_except.is_weekend = True

Дубликатов быть не должно.

Что делал:
Для начала я соединяю LEFT INNER JOIN последовательно таблицы worker <-- work (по worker_id) <-- work_except (по worker_id).

Попытка через фильтрацию:

Делал выборку по date и времени в обеих таблица и сортировки по work.is_weekend и work_except.is_weekend DESC

Однако это не подошло, так как согласно сортировке возвращался список где сначала
work.is_weekend = FALSE и work_except.is_weekend IS NULL - это ОК
Далее work.is_weekend = FALSE и work_except.is_weekend = True - уже неверно согласно требованиям
Далее work.is_weekend = TRUE и work_except.is_weekend IS NULL - тоже неверно согласно требованиям
Далее work.is_weekend = TRUE и work_except.is_weekend = FALSE - тоже неверно согласно требованиям

SELECT
    work.id,
    work_except.id,
    work.time_from,
    work.time_to,
    work.is_weekend,
    work_except.is_weekend
FROM couriers c
    LEFT JOIN worker ON worker.id = work.worker_id
    LEFT JOIN work_except ON worker.id = work_except.worker_id
WHERE
        work.date = <some_date> AND
        work_except.date = <some_date> AND
        work.time_from >= '10:00:00' AND
        work.time_to <= '20:00:00'
ORDER BY
        cws.is_weekend,
        cwse.is_weekend DESC;

Попытка 2.
Пробовал воспользоваться отдельными запросами с условиями объединёнными UNION. Но там стали вовсе выпадать некоторые записи и с сортировкой проблемы.

Подскажите как можно решить эту задачу и какой подход лучше использовать?

P.S. Активно мониторию вопрос. Если необходимы пояснения, пишите, сразу отвечу.

Answer 1

В оригинальном вопросе нет информации о том, по каким условиям объединяются отношения work и work_except. Точнее, есть наверняка неверный ответ «по work_except.worker_id = work_except.worker_id», что не является достаточным условием, так как у одного работника скорее всего несколько дней.

Если предположить, что настоящим условием объединения являются работник и даты, то остаётся всего лишь ввести отдельное сортировочное поле:

WITH all_work AS (
  SELECT work.*
       , COALESCE(work_except.is_weekend, FALSE) AS is_except
    FROM work
    LEFT JOIN work_except
      ON work.worker_id = work_except.worker_id
     AND work.time_from = work_except.time_from
     AND work.time_to = work_except.time_to
     AND work.date = work_except.date
)
SELECT *
     , CASE
         WHEN NOT is_weekend AND NOT is_except THEN 1
         WHEN is_weekend AND NOT is_except     THEN 2
         ELSE 3
       END AS sort_order
  FROM all_work
 ORDER BY sort_order
;

Fiddle.

Дальше можно добавлять условия по дате и времени.

БЛОГ НА HUSL

Некорректная выборка в SQL запросе

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