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

База данных «Компьютерная фирма»

Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, то есть (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM

Вот решение, которое довольно часто встречается у посетителей сайта:

Решение 1.12.1

Консоль

Выполнить

SELECT MAX(model) AS 'model', MIN(model) AS 'model', speed, ram
FROM PC
GROUP BY speed, ram
HAVING MAX(model) > MIN(model);

SELECT MAX(model) AS 'model', MIN(model) AS 'model', speed, ram FROM PC GROUP BY speed, ram HAVING MAX(model) > MIN(model);

    Схема

Не известно, по какой причине выводят только максимальную и минимальную модель для каждой совпадающей пары значений speed, ram. Возможно, в заблуждение вводит результат «правильного» запроса на основной базе.

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

но не

То есть если, скажем, три модели — 1122, 1121, 1135 — имеют одинаковые характеристики, то вывод должен быть таким:

Ниже представлено почти правильное, хотя и громоздкое решение.

Решение 1.12.2

Консоль

Выполнить

SELECT P.model, L.model, P.speed, P.ram
FROM PC P JOIN 
     (SELECT speed, ram
      FROM PC
      GROUP BY speed, ram
      HAVING SUM(speed)/speed = 2 AND 
             SUM(ram)/ram = 2 
      ) S ON P.speed = S.speed AND 
             P.ram = S.ram JOIN 
      PC L ON L.speed = S.speed AND 
              L.ram = S.ram AND 
              L.model < P.model;

SELECT P.model, L.model, P.speed, P.ram FROM PC P JOIN      (SELECT speed, ram       FROM PC       GROUP BY speed, ram       HAVING SUM(speed)/speed = 2 AND              SUM(ram)/ram = 2       ) S ON P.speed = S.speed AND              P.ram = S.ram JOIN       PC L ON L.speed = S.speed AND               L.ram = S.ram AND               L.model < P.model;

    Схема

Здесь в подзапросе S отбираются уникальные пары характеристик (скорость, память), совпадающие у двух ПК (SUM(speed)/speed = 2) — сумма одинаковых значений, деленная на это значение, дает количество ПК. Хотя с тем же успехом можно было написать такое предложение HAVING:

HAVING COUNT(*) = 2

Подзапрос дважды соединяется с таблицей PC по этой паре характеристик. При этом второе соединение выполняется лишь для того, чтобы упорядочить модели (L.model < P.model).

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

Несмотря на то, что решение легко исправить, лучше написать его не в такой избыточной форме.

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

ПиР