Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

Подсказки и решения

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

Консоль

Выполнить

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;

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-значение.

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

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

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