loading..
Русский    English
16:52
листать

Упражнение 57 стр. 1

Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.

Эта задача в чем-то подобна задаче 56, то есть здесь можно допускать те же ошибки в подсчете потопленных кораблей. Однако ситуация усугубляется еще и определением общего числа кораблей в классе.

Решение 3.13.1

Консоль
Выполнить
  1. SELECT c.class, SUM(outc)
  2. FROM Classes c LEFT JOIN
  3. Ships s ON c.class = s.class LEFT JOIN
  4. (SELECT ship, 1 outc
  5. FROM Outcomes
  6. WHERE result = 'sunk') o ON s.name = o.ship OR
  7. c.class = o.ship
  8. GROUP BY c.class
  9. HAVING COUNT(*) > 2 AND
  10. SUM(outc) IS NOT NULL;

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

Далее выполняется еще одно левое соединение с набором потопленных кораблей по предикату

  1. ON s.name = o.ship OR c.class = o.ship

В вычисляемый столбец заносится 1, если имя потопленного корабля совпадает либо с именем корабля, либо с именем класса из полученного ранее набора. Таким образом, мы здесь и пытаемся учесть головные корабли.

Наконец, выполняется группировка по классам с отбором по числу кораблей (строк) класса и подсчитывается сумма потопленных кораблей (единиц в столбце outs). Автор решения предлагает рациональный способ вычислить в одной группировке и общее число кораблей, и количество потопленных кораблей в классе. Предикат:

  1. SUM(outc) IS NOT NULL

в соответствии с условием задачи убирает из результата классы, не имеющие потопленных кораблей.

Те, кто внимательно читал анализ предыдущих задач, уже догадались, в чем дело. Правильно, проблема в предикате второго соединения. Однако не только в этом.

Рассмотрим следующий вариант данных. Пусть для некоторого класса class_N в таблице Ships имеется два корабля: ship_1 и ship_2. Кроме того, в таблице Outcomes есть потопленный корабль ship_1 и оставшийся на плаву головной — class_N.

Первое соединение даст:

Class Ship
Class_N ship_1
Class_N ship_2

Выполняем второе соединение:

Class ship outs
Class_N ship_1 1
Class_N ship_2 NULL

В результате этот класс вообще не попадет в результирующий набор, так как не будет выполнено условие COUNT(*) > 2, хотя на самом деле корабля 3. Причина ошибки заключается в том, что мы выполняем соединение только по потопленным кораблям, одновременно подсчитывая общее число кораблей.

Давайте теперь немного изменим данные в примере. Пусть и головной корабль class_N тоже потоплен. Тогда результатом соединения будет:

class ship outs
class_N ship_1 1
class_N ship_2 NULL
class_N ship_1 1
class_N ship_2 1

Последние две строки будут получены в результате соединения со строкой потопленного головного корабля, так как предикат c.class = o.ship дает «истину». Таким образом, мы вместо одной строки для головного корабля получаем по строке на каждый корабль класса из таблицы Ships. Итого, вместо

class outs
class_N 2

имеем

class outs
class_N 3

Вы можете попытаться исправить это решение или использовать другой подход на базе внутреннего соединения и объединения.

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

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