Упражнение 51 (подсказки и решения)

Здесь мы хотим привести одно интересное решение, которое использует только соединения:

SELECT DISTINCT CASE
    WHEN sh.name IS NOT NULL
    THEN sh.name
    ELSE ou.ship
    END name
FROM Ships sh 
    FULL JOIN Outcomes ou ON ou.ship = sh.name 
    LEFT OUTER JOIN Classes cl ON ou.ship = cl.class 
                               OR sh.class = cl.class 
    LEFT OUTER JOIN Classes cl2 ON cl.displacement = cl2.displacement 
                               AND cl.numGuns < cl2.numGuns
WHERE cl.class IS NOT NULL 
    AND cl2.class IS NULL;

Достаточно сложная логика этого решения будет, безусловно, полезна для обучения на данном этапе. Начнем, как обычно, с предложения FROM. Полное соединение (FULL JOIN) дает нам все корабли из базы данных. При этом возможны следующие варианты:

• корабль присутствует только в таблице Ships;
• корабль присутствует в обеих таблицах;
• корабль присутствует только в таблице Outcomes.

Этим трем случаям соответствуют следующие строки результирующего набора (показаны только значимые в данном случае столбцы):

Затем выполняется левое соединение с таблицей классов по предикату:

ou.ship = cl.class OR
sh.class = cl.class

То есть либо имя корабля из Outcomes должно совпадать с именем класса (висящие головные корабли), либо класс корабля из таблицы Ships. Результат соединения можно проиллюстрировать следующей таблицей:

Третья строка таблицы соответствует случаю, когда класс корабля неизвестен (он не является головным!), а четвертая описывает случай головного корабля, отсутствующего в таблице Ships. Забегая немного вперед, заметим, что третья строка не может нам дать никакой информации о водоизмещении и числе орудий такого корабля, поэтому она отфильтровывается следующим предикатом в предложении WHERE рассматриваемого решения:

cl.class IS NOT NULL

Наконец, последнее левое соединение опять выполняется с таблицей классов, но уже по другому предикату:

cl.displacement = cl2.displacement AND
cl.numGuns < cl2.numGuns

Первое условие предиката очевидно — мы соединяем строки по равенству значений водоизмещения, так как нам нужно находить максимум в группе кораблей, имеющих одинаковое водоизмещение. Чтобы понять смысл второго условия, опять обратимся к примеру. Дополним нашу таблицу необходимыми столбцами и рассмотрим результат соединения по данному предикату на примере, скажем, первой строки предыдущей таблицы при следующих значениях числа орудий у классов кораблей водоизмещением 30 000 тонн:

Таким образом, корабли класса, имеющего максимальное число орудий в свой группе по водоизмещению, будут иметь NULL-значение в столбцах, относящихся к таблице cl2 (при левом соединении!), в том числе в столбце cl2.class, так как предикат не удовлетворяется. Именно этот критерий и используется в предложении WHERE для отбора записей, отвечающих условиям задачи (cl2.class IS NULL).

Наконец, оператор CASE формирует имя корабля в окончательном результирующем наборе, выбирая из двух вариантов — столбцы ship или name — тот, в котором находится не NULL-значение.

Если изложенное выше вам не вполне понятно, почитайте о внешних соединениях).

Интересное решение, но учитывающее не все возможные варианты данных. Оно не будет давать верный результат, если имеется класс, число орудий на кораблях которого, нам не известно. Обратите внимание на схему данных: столбец numGuns допускает NULL-значения! Предлагаем вам самостоятельно проанализировать причину ошибки и исправить рассмотренное решение.

Вернуться к обсуждению упражнения 51

Решить задачу на SQL-EX.RU