Книги и статьи по SQL Rambler's Top100 Switch language to: English 29 октября 2020 г. 4:49:35


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Характерные ошибки при решении упражнений. Задача 57

Моисеенко С.И.

Для каждого класса, имеющего не менее трех кораблей в базе данных, определите число кораблей этого класса, потопленных в сражениях, если такие были. Вывести: класс и число потопленных кораблей.

Рассмотрим следующее решение, не принимаемое системой проверки:

SELECT c.class, SUM(outc) FROM Classes c 
   LEFT JOIN Ships s ON c.class=s.class
    LEFT JOIN (SELECT ship, 1 outc FROM Outcomes WHERE result='sunk') o
        ON s.name=o.ship OR c.class=o.ship GROUP BY c.class
HAVING COUNT(*) > 2 AND SUM(outc) IS NOT NULL

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

Далее выполняется еще одно левое соединение с набором потопленных кораблей по предикату

ON s.name=o.ship OR c.class=o.ship

В вычисляемый столбец заносится 1, если имя потопленного корабля совпадает либо с именем корабля, либо с именем класса из полученного ранее набора. Таким образом, мы здесь и пытаемся учесть головные корабли.

Наконец, выполняется группировка по классам с отбором по числу кораблей (строк) класса и подсчитывается сумма потопленных кораблей (единиц в столбце outs). Насколько я понял, автор предлагает рациональный способ вычислить в одной группировке и общее число кораблей, и количество потопленных кораблей в классе. Предикат

SUM(outc) IS NOT NULL

в соответствии с условием задачи убирает из результата классы, не имеющие потопленных кораблей.

Те, кто читал предыдущий номер рассылки с анализом задачи 46, уже догадались, в чем дело. Правильно, проблема в предикате второго соединения. Однако не только в этом.

Рассмотрим следующий вариант данных. Пусть для некоторого класса class_N в таблице Ships имеется два корабля: ship_1 и ship_2. Кроме того, в таблице Outcomes есть потопленный корабль ship_1 и оставшийся целым головной - class_N.

Первое соединение даст:
class ship
Class_N ship_1
Class_N ship_2

Выполняем второе соединение:

class ship outs
Class_N ship_1 1
Class_N ship_2 NULL

В результате этот класс вообще не попадет в результирующий набор, т.к. не будет выполнено условие COUNT(*) > 2, хотя на самом деле корабля 3. Причина ошибки заключается в том, что мы выполняет соединение только по потопленным кораблям, одновременно подсчитывая общее число кораблей.

Давайте теперь немного изменим данные в примере. Пусть и головной корабль class_N тоже потоплен. Тогда результатом соединения будет:

Class ship Outs
class_N ship_1 1
class_N ship_2 NULL
class_N ship_1 1
class_N ship_2 1

Последние две строки будут получены в результате соединения со строкой потопленного головного корабля, т.к. предикат c.class=o.ship дает "истину". Таким образом, мы вместо одной строки для головного корабля получаем по строке на каждый корабль класса из таблицы Ships. Итого, вместо

class outs
class_N 2

имеем

class outs
class_N 3

Вы можете попытаться исправить это решение. Или использовать другой подход на базе внутреннего соединения и объединения.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Перейти к решению задачи #57

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.