loading..
Русский    English
16:36
листать

Упражнение 37 стр. 4

Решение 3.2.4

Подход на основе объединения не является единственно возможным. Следующее решение использует соединения.

Консоль
Выполнить
  1. SELECT Classes.class
  2. FROM Outcomes RIGHT OUTER JOIN
  3. Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN
  4. Ships ON Classes.class = Ships.class
  5. GROUP BY Classes.class
  6. HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

Правое соединение таблиц Outcomes и Classes дает нам головные корабли, при этом столбец Outcomes.ship будет содержать NULL-значение, если головного корабля нет в таблице Outcomes. Затем выполняется левое соединение таблиц Classes и Ships по внешнему ключу. Столбец Ships.name будет содержать NULL-значение, если класс не имеет кораблей в таблице Ships. Полученный набор записей группируется по классу, после чего выполняется фильтрация по предикату

  1. COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1

Остановимся подробнее на этом элегантном приеме.

Примечание:

Автор не иронизирует, когда говорит «красивый», «элегантный» и т. д. о неправильных подходах к решению задачи. Большинство рассматриваемых здесь запросов писалось профессионалами, в совершенстве владеющих языком SQL. Анализировать такие решения — хорошая школа для начинающих. А ошибки в этих решениях как правило связаны с игнорированием той или иной особенности предметной области, и зачастую легко исправляются чисто косметическими средствами.

Чтобы все было до конца ясно, приведем примеры четырех возможных вариантов строк (взятых из доступной базы данных кроме последнего случая), которые получаются в результате соединения. Вот они:

Ship class name
Bismarck Bismarck NULL
Tennessee Tennessee California
Tennessee Tennessee Tennessee
NULL Yamato Musashi
NULL Yamato Yamato
NULL Class_1 NULL

NULL в столбце name для класса Bismarck означает, что головной корабль имеется только в таблице Outcomes. Корабли California и Tennessee класса Tennessee имеются в таблице Ships, при этом головной корабль есть также в таблице Outcomes. В третьем случае два корабля класса Yamato присутствуют в таблице Ships, в таблице же Outcomes нет головного корабля данного класса. Для четвертого случая класс Class_1 не имеет кораблей в базе данных.

Вернемся к предикату. Функция COALESCE (Outcomes.ship, Ships.name) вернет первое не NULL значение своих аргументов или NULL, если оба аргумента есть NULL-значение. Подробнее о функции COALESCE можно почитать в пункте 5.10.

Агрегатная функция COUNT, имеющая аргумент, вернет количество не NULL-значений аргумента в группе. Поэтому для класса Bismarck мы получим 1, для Tennessee и Yamato — 2 и, наконец, для Class_1 — 0. В результирующий набор из этих четырех классов попадает только Бисмарк, так как только он отвечает предикату.

Это решение не содержит ошибки предыдущего решения 3.2.3, то есть головной корабль, присутствующий как в таблице Ships, так и в таблице Outcomes, будет учтен только один раз. Это обусловлено тем, что при используемом соединении в результирующем наборе будет только одна строка.

Внимание:

Всегда ли справедливо для нашей базы данных последнее утверждение? Приведите пример данных, когда это будет не так. В этом, кстати, состоит еще одна ошибка данного решения.

Кроме того, в этом решении содержится та же ошибка, что и в примере 3.2.1. Если добавить в базу данных указанную там строку, то в результате соединения мы получим только одну строку на два корабля класса Бисмарк:

Ship Class name
Bismarck Bismarck Tirpitz

Если вы еще не обнаружили ошибки, упомянутой на врезке, загляните в ПиР.

ПиР

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

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