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

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

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

Решение 3.12.1

Консоль

Выполнить

SELECT aa.class, SUM(aa.sunks) Sunks 
FROM (
-- 1
SELECT c.class, COUNT(a.ship) sunks 
FROM Outcomes a INNER JOIN 
Ships b ON a.ship = b.name INNER JOIN 
Classes c ON b.class = c.class
WHERE a.result = 'sunk'
GROUP BY c.class
UNION
-- 2
SELECT c.class, COUNT(a.ship)
FROM Outcomes a INNER JOIN 
Classes c ON a.ship = c.class
WHERE a.result = 'sunk'
GROUP BY c.class
UNION
-- 3
SELECT c.class, 0 
FROM Classes c
) aa
GROUP BY aa.class;

SELECT aa.class, SUM(aa.sunks) Sunks FROM ( -- 1 SELECT c.class, COUNT(a.ship) sunks FROM Outcomes a INNER JOIN Ships b ON a.ship = b.name INNER JOIN Classes c on b.class = c.class WHERE a.result = 'sunk' GROUP BY c.class UNION -- 2 SELECT c.class, COUNT(a.ship) FROM Outcomes a INNER JOIN Classes c ON a.ship = c.class WHERE a.result = 'sunk' GROUP by c.class UNION -- 3 SELECT c.class, 0 FROM Classes c ) aa GROUP BY aa.class;

    Схема

В подзапросе предложения FROM объединяются три таблицы:

Класс и число потопленных кораблей, которые есть в таблице Ships.

Класс и число потопленных головных кораблей класса. Здесь уже есть «излишество», а именно: нет необходимости использовать группировку и соответственно функцию COUNT, так как у класса может быть только один головной корабль, да и потоплен корабль может быть только однажды.

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

Объединение с использованием UNION устраняет дубликаты, что, по мнению автора решения, позволяет корректно обработать ситуацию, когда потопленный головной корабль присутствует в таблице Ships. Наконец, выполняется группировка по классам с суммированием. При этом последний набор не дает вклада в окончательный результат, если в классе имеются потопленные корабли, что правильно.

Однако ошибка кроется в том, что объединяются двухатрибутные кортежи {класс, число потопленных кораблей}. Поэтому если в некотором классе (опять «Бисмарк») имеется два потопленных корабля, причем головной корабль отсутствует в Ships, то объединяться будут два одинаковых кортежа

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

Но это еще не все. Даже головной корабль мы можем посчитать дважды, если он присутствует в Ships. Это справедливо для случая, когда есть и другие корабли этого класса, потопленные в сражениях. Давайте опять возьмем для примера «Бисмарк», только теперь он присутствует также в таблице Ships. Пусть есть и еще один потопленный корабль (естественно, не головной) этого класса. Тогда первый набор даст:

а второй:

В результате мы получим

хотя на самом деле корабля всего два.