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

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

Решение 3.7.4

Консоль

Выполнить

SELECT DISTINCT battle 
FROM (SELECT battle, country 
 FROM (SELECT battle, country 
 FROM Outcomes INNER JOIN 
 Classes ON ship = class 
 UNION 
 SELECT battle, country 
 FROM Outcomes o INNER JOIN 
 Ships s ON o.ship = s.name INNER JOIN 
 Classes c ON s.class = c.class
 ) x 
 GROUP BY battle, country 
 HAVING COUNT(*) > 2
 ) y;

SELECT DISTINCT battle FROM (SELECT battle, country FROM (SELECT battle, country FROM Outcomes INNER JOIN Classes ON ship = class UNION SELECT battle, country FROM Outcomes o INNER JOIN Ships s ON o.ship = s.name INNER JOIN Classes c ON s.class = c.class ) x GROUP BY battle, country HAVING COUNT(*) > 2 ) y;

    Схема

Во внутреннем подзапросе объединяются два запроса. В первом из них

Консоль

Выполнить

SELECT battle, country 
 FROM Outcomes INNER JOIN 
 Classes ON ship = class;

SELECT battle, country FROM Outcomes INNER JOIN Classes ON ship = class;

    Схема

Консоль

Выполнить

SELECT battle, country 
 FROM Outcomes o INNER JOIN 
 Ships s ON o.ship = s.name INNER JOIN 
 Classes c ON s.class = c.class;

SELECT battle, country FROM Outcomes o INNER JOIN Ships s ON o.ship = s.name INNER JOIN Classes c ON s.class = c.class;

    Схема

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

Первое, что приходит в голову, — написать UNION ALL вместо UNION, то есть учесть все дубликаты. Но, как уже понятно из предыдущего обсуждения, тогда для одного головного корабля, участвующего в некотором сражении, мы получим две строки, если этот корабль присутствует еще и в таблице Ships.

Как поступить? Автор предлагает два подхода. При первом подходе мы оставляем UNION, но подсчитываем не страны, а корабли. Тогда устранение дубликатов будет правильным. При втором подходе автор предполагает использование UNION ALL, но тогда нужно в одном из объединяемых запросов проверять, чтобы учитываемый корабль не присутствовал в другой таблице, тем самым, подсчитывая его один раз.

Какой из способов предпочесть, зависит не только от наших предпочтений, но и от стоимости плана выполнения запроса. Предлагаем самостоятельно оценить планы, предварительно решив задачу двумя описанными способами.

ПиР

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