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

База данных «Корабли»

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

Эта задача в чем-то подобна задаче 56, то есть здесь можно допускать те же ошибки в подсчете потопленных кораблей. Однако ситуация усугубляется еще и определением общего числа кораблей в классе.

Решение 3.13.1

Консоль

Выполнить

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;

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

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

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

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

Первое соединение даст:

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

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

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

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

имеем

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

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