loading..
Русский    English
06:23
листать

Упражнение 51

Найдите названия кораблей, имеющих наибольшее число орудий среди всех кораблей такого же водоизмещения (учесть корабли из таблицы Outcomes).

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

Консоль
Выполнить
  1. SELECT name
  2. FROM (SELECT O.ship AS name, numGuns, displacement
  3. FROM Outcomes O INNER JOIN
  4. Classes C ON O.ship = C.class AND
  5. O.ship NOT IN (SELECT name
  6. FROM Ships
  7. )
  8. UNION
  9. SELECT S.name AS name, numGuns, displacement
  10. FROM Ships S INNER JOIN
  11. Classes C ON S.class = C.class
  12. ) OS INNER JOIN
  13. (SELECT MAX(numGuns) AS MaxNumGuns, displacement
  14. FROM Outcomes O INNER JOIN
  15. Classes C ON O.ship = C.class AND
  16. O.ship NOT IN (SELECT name
  17. FROM Ships
  18. )
  19. GROUP BY displacement
  20. UNION
  21. SELECT MAX(numGuns) AS MaxNumGuns, displacement
  22. FROM Ships S INNER JOIN
  23. Classes C ON S.class = C.class
  24. GROUP BY displacement
  25. ) GD ON OS.numGuns = GD.MaxNumGuns AND
  26. OS.displacement = GD.displacement;

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

  1. O.ship NOT IN (SELECT name
  2. FROM Ships
  3. )

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

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

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

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

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

ПиР

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100