На главную страницу
Характерные ошибки при решении упражнений.
Задача 56
Моисеенко С.И.
Для каждого класса определите число кораблей этого класса, потопленных в
сражении. Вывести: класс и число потопленных кораблей.
Рассмотрим следующее решение этой задачи:
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
В подзапросе объединяются три таблицы:
1. Класс и число потопленных кораблей, которые есть в таблице Ships.
2. Класс и число потопленных головных кораблей этого класса. Здесь уже есть
"излишество", а именно: нет необходимости использовать группировку и
соответственно функцию COUNT, т.к. у класса может быть только один головной
корабль, да и потоплен корабль может быть только однажды.
3. Каждый класс с нулевым количеством потопленных кораблей. Это позволяет
учесть те классы, которые не имеют потопленных кораблей и, следовательно, не
попадают в предыдущие два набора записей.
Объединение с использованием UNION устраняет дубликаты, что, по мнению автора,
позволяет корректно обработать ситуацию, когда потопленный головной корабль
имеется и в таблице Ships. Наконец, выполняется группировка по классам с
суммированием. При этом последний набор не даст вклада в окончательный
результат, если в классе имеются потопленные корабли, что правильно.
Однако ошибка кроется в том, что объединяются двухатрибутные кортежи {класс,
число потопленных кораблей}. Поэтому если в некотором классе (например,
Бисмарк) имеется два потопленных корабля, причем головной корабль отсутствует в
Ships, то объединяться будут два одинаковых кортежа
Бисмарк 1
И после устранения дубликатов мы получаем один потопленный корабль вместо двух.
Но это не все. Даже головной корабль мы можем посчитать дважды, если он
присутствует и в Ships. Это справедливо для случая, если есть и другие корабли
этого класса, потопленные в сражениях. Давайте опять возьмем для примера
Бисмарк, только теперь он присутствует также в таблице Ships. Пусть есть и еще
один потопленный корабль (не головной) этого класса. Тогда первый набор даст
Бисмарк 2
а второй
Бисмарк 1
В результате мы получим
Бисмарк 3
Хотя на самом деле корабля всего два.
Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
Перейти к
решению задачи #56
На главную страницу