На главную страницу
Характерные ошибки при решении упражнений.
Задача 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. Итого, вместо
имеем
Вы можете попытаться исправить это решение. Или использовать другой подход на
базе внутреннего соединения и объединения.
Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
Перейти к
решению задачи #57
На главную страницу