loading..
Русский    English
10:17
листать

Упражнение 10 (подсказки и решения)

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

Консоль
Выполнить
  1. SELECT TOP 1 WITH TIES model, price
  2. FROM Printer
  3. ORDER BY price DESC;

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

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

  1. LIMIT N [OFFSET M]
где
N - число первых строк, возвращаемых запросом в порядке, заданном сортировкой;
M - число строк, пропускаемых перед началом вывода.

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

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

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

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

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

model price
1434 290.00
1433 270.00
1408 270.00
1401 150.00

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

Консоль
Выполнить
  1. SELECT model, price
  2. FROM Printer WHERE code NOT IN(
  3. SELECT TOP 1 code
  4. FROM Printer
  5. ORDER BY price DESC);
Т.е. мы выбираем все строки, кроме той, которая идёт первой при сортировке цены по убыванию.

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

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

  1. ORDER BY price DESC, code

или

  1. ORDER BY price DESC, model, code

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

Решить упражнение на SQL-EX.RU

Рекомендуемые упражнения: 90

Bookmark and Share
Тэги:
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 INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.