Упражнение 56 стр. 5

Решение 3.12.5

Консоль

Выполнить

SELECT t1.class, COUNT(*) AS cnt 
FROM (SELECT a.class, b.name 
      FROM Classes a LEFT JOIN 
-- соединение с Ships без головных кораблей:
           Ships b ON a.class = b.class AND 
                      a.class <> b.name
      ) AS t1 JOIN 
-- соединение либо по классу для головных кораблей, либо по имени
      Outcomes t2 ON t1.class = t2.ship OR 
                     t1.name = t2.ship
WHERE result = 'sunk'
GROUP BY t1.class
-- Выбираются классы кораблей, которые не попали в первый запрос.
-- Это классы, не имеющие потопленных кораблей.
UNION
SELECT class, '0' 
FROM Classes 
WHERE class NOT IN (SELECT DISTINCT t1.class 
                    FROM (SELECT a.class, b.name 
                          FROM Classes a LEFT JOIN 
                               Ships b ON a.class = b.class AND 
                                          a.class <> b.name
                          ) AS t1 JOIN 
                          Outcomes t2 ON t1.class = t2.ship OR 
                                         t1.name = t2.ship
                    WHERE result = 'sunk'
                    );

Решение 3.12.6

Консоль

Выполнить

SELECT d.class class, (SELECT COUNT(f.result)
                       FROM (SELECT c.result
                             FROM Ships b LEFT OUTER JOIN 
                                  Outcomes c ON (b.name = c.ship)
                             WHERE c.result = 'sunk' AND
                                   d.class = b.class
                             UNION ALL
                             SELECT c.result
                             FROM Outcomes c
                             WHERE c.result = 'sunk' AND
                                   d.class = c.ship
                             ) f
                       ) Sunks
FROM Classes d;

Для анализа двух последних решений — 3.12.5 и 3.12.6 — рассмотрим следующие варианты данных. В таблице Ships (показаны только принципиальные для анализа столбцы):

name	class
ship1_class_1	class_1
ship1_class_1	class_1

В таблице Outcomes:

ship	result
ship1_class_1	sunk
class_1	sunk

Тогда согласно предикату соединения в решении 3.12.5

     ON t1.class = t2.ship OR 
        t1.name = t2.ship

в результирующий набор дважды попадет корабль ship1_class_1 из таблицы Ships, так как для первой строки в таблице Outcomes у него совпадает имя корабля, а для второй — название класса. В результате получим 3 потопленных корабля, хотя на самом деле их только 2.

Решение задачи 3.12.6 даст нам здесь правильный результат, поскольку первый запрос в объединении (соединение по имени корабля) даст только ship1_class_1, а второй — только class_1. Однако это решение тоже не верно, что будет продемонстрировано на другом варианте данных.

В таблице Ships

name    class
ship1_class_2    class_2
class_2    class_2

В таблице Outcomes:

ship    result
ship1_class_2    sunk
class_2    sunk

Первый запрос в объединении даст нам оба потопленных корабля класса class_2, а второй — головной корабль этого класса. Поскольку при объединении используется UNION ALL, то головной корабль дважды будет учтен в результирующем наборе, в результате чего мы опять получаем 3 вместо 2. Косметическое исправление UNION ALL на UNION не делает решение верным, так как здесь возникает та же ошибка, что и в решении 3.12.4, когда для любого количества потопленных кораблей класса в результат попадает только 1.

Кстати, решение 3.12.5 на этих данных тоже дает значение 3, но по другой причине, описанной выше.

ПиР

Решить упражнение на SQL-EX.RU

Страницы:

ошибки UNION ALL Упражнение 56

Предыдущая [Упражнение 55]

[Упражнение 57] Следующая

SQL Задачи и решения

Учебник. Сергей Моисеенко.

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

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

Упражнение 56 стр. 5

	Содержание	Вход

SQL Задачи и решения Учебник. Сергей Моисеенко.