Книги и статьи по SQL Rambler's Top100 Switch language to: English 28 марта 2020 г. 17:49:09


www.sql-ex.ru
Skip Navigation Links  

 

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

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

Характерные ошибки при решении упражнений. Задача 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

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

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


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

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