loading..
Русский    English
05:20
листать

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

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

Консоль
Выполнить
  1. SELECT DISTINCT CASE
  2. WHEN sh.name IS NOT NULL
  3. THEN sh.name
  4. ELSE ou.ship
  5. END name
  6. FROM Ships sh FULL JOIN
  7. Outcomes ou ON ou.ship = sh.name LEFT OUTER JOIN
  8. Classes cl ON ou.ship = cl.class OR
  9. sh.class = cl.class LEFT OUTER JOIN
  10. Classes cl2 ON cl.displacement = cl2.displacement AND
  11. cl.numGuns < cl2.numGuns
  12. WHERE cl.class IS NOT NULL AND
  13. cl2.class IS NULL;

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

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

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

Ship name
NULL ship_1
ship_2 ship_2
ship_3 NULL

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

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

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

ship name sh.class cl.class
NULL ship_1 class_1 class_1
ship_2 ship_2 class_1 class_1
ship_3 NULL NULL NULL
class_1 NULL class_1 class_1

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

  1. cl.class IS NOT NULL

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

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

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

class_1 16
class_2 10
class_3 14

ship Name sh.class cl.class displacement cl2.class cl.numGuns cl2.numGuns
NULL ship_1 class_1 class_1 30000 NULL 16 NULL
NULL ship_2 class_2 class_2 30000 class_1 10 16
NULL ship_2 class_2 class_2 30000 class_3 10 14
NULL ship_3 class_3 class_3 30000 class_1 14 16

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

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

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

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

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

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


Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100