Упражнение 51

Решение 3.8.1

Не очень оптимальное решение и, к тому же, содержащее ошибку.

SELECT name
FROM (SELECT O.ship AS name, numGuns, displacement
    FROM Outcomes O 
        INNER JOIN Classes C ON O.ship = C.class 
                            AND O.ship NOT IN (SELECT name
                                                    FROM Ships
                                                    )
    UNION
    SELECT S.name AS name, numGuns, displacement
    FROM Ships S 
        INNER JOIN Classes C ON S.class = C.class
    ) OS 
    INNER JOIN
    (SELECT MAX(numGuns) AS MaxNumGuns, displacement
    FROM Outcomes O 
        INNER JOIN Classes C ON O.ship = C.class 
            AND O.ship NOT IN (SELECT name
                                FROM Ships
                                )
    GROUP BY displacement
    UNION
    SELECT MAX(numGuns) AS MaxNumGuns, displacement
    FROM Ships S 
        INNER JOIN Classes C ON S.class = C.class
    GROUP BY displacement
    ) GD ON OS.numGuns = GD.MaxNumGuns 
        AND OS.displacement = GD.displacement;

В предложении FROM данного решения соединяются два подзапроса. В первом из них определяется имя, число орудий и водоизмещение всех имеющихся в базе данных кораблей. Эти корабли собираются по двум таблицам — Ships и Outcomes (головные корабли). При этом выполняется неправильная и излишняя проверка на дубликаты:

O.ship NOT IN (SELECT name
FROM Ships
)

Почему неправильная? Да потому, что она все равно оставляет дубликаты, учитывая головной корабль столько раз, сколько раз он участвовал в сражениях. Ну, а излишней она является потому, что предложение UNION все равно устранит дубликаты. Это в данном случае оказалось совсем нелишним, в результате чего подзапрос, хотя и не оптимальный, дает то, что и предполагалось по алгоритму.

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

Соединение выполняется по совпадению числа орудий и водоизмещения в строках этих подзапросов.

Логика построения решения вполне верная; не верна реализация. Чтобы доказать это, как обычно рассмотрим контрпример. Другими словами, приведем пример данных, на котором этот запрос даст неверное решение задачи. Итак, пусть только в таблице Ships есть корабли водоизмещением 40 000 тонн с максимальным числом орудий 16, и только в таблице Outcomes имеется головной корабль водоизмещения 40 000 тонн и числом орудий 17. Тогда второй подзапрос из соединения даст нам две строки:

Поскольку это не дубликаты, обе эти строки будут присутствовать в результирующем наборе. В результате соединения мы получим не только корабли с максимальным числом орудий для данного водоизмещения — 17, но и корабли, имеющие на вооружении 16 орудий. Узнаете ошибку? Она уже встречалась ранее: сначала нужно делать объединение, а потом группировку.

ПиР

Решить задачу на SQL-EX.RU