Упражнение 10 (подсказки и решения)
Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции TOP N (SQL Server), которая позволяет отобрать из отсортированного набора первые N строк. Аналогичные конструкции имеются в диалектах SQL многих СУБД. В комитете по разработке стандартов было даже зафиксировано предложение о включение подобной конструкции в стандарт языка. Так что не исключено, что, когда вы это читаете, данная конструкция уже будет стандартизована. А вот и решение:
SELECT TOP 1 WITH TIES model, price
FROM Printer
ORDER BY price DESC;
[[ column ]] |
---|
NULL [[ value ]] |
Итак, выполняется сортировка по убыванию цены. В результирующий набор попадает одна (первая — TOP 1) строка. Однако остается проблема, когда несколько принтеров из таблицы будут иметь одинаковую максимальную цену. Проблема решается при помощи предложения WITH TIES, которое включит в результирующий набор не только N строк (в нашем случае одну), но и все ниже идущие строки, у которых значения в полях сортировки (у нас — price) совпадают со значениями N-ой строки (у нас — первой).
В PostgreSQL/MySQL для ограничения количества строк, возвращаемых запросом, используется конструкция (следующая после ORDER BY)
LIMIT N [OFFSET M]
где
N - число первых строк, возвращаемых запросом в порядке, заданном сортировкой;
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);
[[ column ]] |
---|
NULL [[ value ]] |
Заметим, что в отличие от MySQL, в PostgreSQL предложение OFFSET может использоваться и при отсутствии предложения LIMIT. В этом случае возвращаться будут все строки запроса кроме первых M. Так, например, чтобы вывести все строки кроме первой строки с максимальной ценой, можно написать:
SELECT model, price
FROM Printer
ORDER BY price DESC OFFSET 1;
[[ column ]] |
---|
NULL [[ value ]] |
model | price |
---|---|
1434 | 290 |
1433 | 270 |
1408 | 270 |
1401 | 150 |
При использовании SQL Server эту же задачу можно решить так:
SELECT model, price
FROM Printer where code NOT IN(SELECT TOP 1 code
FROM Printer
ORDER BY price DESC);
[[ column ]] |
---|
NULL [[ value ]] |
Т.е. мы выбираем все строки, кроме той, которая идёт первой при сортировке цены по убыванию.
А теперь попробуйте решить эту задачу под MySQL. :-)
При решении последней задачи следует обратить внимание на неточность ее постановки. В результате чего представленное решение на одних и тех же данных может давать разные результаты при разных запусках. В частности, у нас имеется две строки с максимальной ценой, и какая из них будет выводиться зависит от порядка, в котором СУБД будет извлекать строки. А этот порядок может меняться в зависимости от выбранного оптимизатором плана. Чтобы сделать постановку и результат(!) однозначными, следует указать в условии задачи однозначный порядок сортировки. Эту однозначность всегда обеспечит включение первичного ключа в конец списка столбцов сортировки, например:
ORDER BY price DESC, code
или
ORDER BY price DESC, model, code
Перейти к обсуждению упражнения 10
Решить упражнение на SQL-EX.RU
Рекомендуемые упражнения: 90