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

Подсказки и решения

Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции TOP N (SQL Server), которая позволяет отобрать из отсортированного набора первые N строк. Аналогичные конструкции имеются в диалектах  Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL многих СУБД. В комитете по разработке стандартов было даже зафиксировано предложение о включение подобной конструкции в стандарт языка. Так что не исключено, что к моменту выхода этой книги в свет данная конструкция уже будет стандартизована. А вот и решение:

Консоль

Выполнить

SELECT TOP 1 WITH TIES model, price
FROM Printer
ORDER BY price DESC;

SELECT TOP 1 WITH TIES model, price FROM Printer ORDER BY price DESC;

    Схема

Итак, выполняется сортировка по убыванию цены. В результирующий набор попадает одна (первая  — TOP 1) строка. Однако остается проблема, когда несколько принтеров из таблицы будут иметь одинаковую максимальную цену. Проблема решается при помощи предложения WITH TIES, которое включит в результирующий набор не только N строк (в нашем случае одну), но и все ниже идущие строки, у которых значения в полях сортировки (у нас — price) совпадают со значениями N-ой строки (у нас — первой).

В PostgreSQL/MySQL для ограничения количества строк, возвращаемых запросом, используется конструкция (следующая после ORDER BY)

LIMIT N [OFFSET M]

Если предложение OFFSET отсутствует, то выводиться будут N строк, начиная с первой, в противном случае - N строк, начиная с M+1.

Конструкций, подобных WITH TIES, в этих СУБД нет. Поэтому для решения рассматриваемой задачи способом через сортировку так или иначе придется использовать подзапрос:
   

SELECT model, price
    FROM Printer WHERE price =
      (SELECT price FROM Printer ORDER BY price DESC LIMIT 1);

Заметим, что в отличие от MySQL, в PostgreSQL предложение OFFSET может использоваться и при отсутствии предложения LIMIT. В этом случае возвращаться будут все строки запроса кроме первых M. Так, например, чтобы вывести все строки кроме первой строки с максимальной ценой, можно написать:

SELECT model, price
    FROM Printer
    ORDER BY price DESC OFFSET 1;

При использовании SQL Server эту же задачу можно решить так:

Консоль

Выполнить

SELECT model, price
FROM Printer WHERE code NOT IN(
SELECT TOP 1 code
FROM Printer
ORDER BY price DESC);

SELECT model, price FROM Printer where code NOT IN( SELECT TOP 1 code FROM Printer ORDER BY price DESC);

    Схема

А теперь попробуйте решить эту задачу под MySQL. :-)

При решении последней задачи следует обратить внимание на неточность ее постановки. В результате чего представленное решение на одних и тех же данных может давать разные результаты при разных запусках. В частности, у нас имеется две строки с максимальной ценой, и какая из них будет выводиться зависит от порядка, в котором СУБД будет извлекать строки. А этот порядок может меняться в зависимости от выбранного оптимизатором плана. Чтобы сделать постановку и результат! однозначными, следует указать в условии задачи однозначный порядок сортировки. Эту однозначность всегда обеспечит включение первичного ключа в конец списка столбцов сортировки, например:

ORDER BY price DESC, code

или

ORDER BY price DESC, model, code

Перейти к обсуждению упражнения 10