4.7.6Раздел order by и ключевое слово top

Раздел определяет порядок сортировки записей, включенных в выборку:

…ORDER BY поле1 [ASC / DESC]

[, поле2 [ASC / DESC] [,…]]

Особенности:

Раздел не является обязательным, однако он обязательно используется с предикатом TOP.

По умолчанию сортировка идет по возрастанию, можно явно указать возрастающую сортировку, записав ASC. Ключ DESC задает сортировку по убыванию.

Порядок перечисления полей задает иерархию уровней сортировки.

ORDER BY – последняя директива в запросе.

Примеры.

Отсортировать записи по полю ФИО в алфавитном порядке:

SELECT *

FROM R1

ORDER BY ФИО;

Отсортировать записи в порядке убывания значений в поле Оценка:

SELECT *

FROM R1

ORDER BY Оценка DESC;

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

Например, выбрать из таблицы СТУДЕНТЫ (Номер_зачетки, Шифр_группы, ФИО, Год_выпуска, Средний_балл) 25 лучших студентов выпуска 2005 года:

SELECT TOP 25 ФИО, Шифр_группы

FROM СТУДЕНТЫ

WHERE Год_выпуска = 2005

ORDER BY Средний_балл DESC;

Число, используемое в предикате TOP, должно быть целым без знака. Без директивы ORDER BY в выборку попали бы любые 25 студентов выпуска 2005 года. Предикат TOP не разделяет записи, имеющие одинаковые значения при упорядочивании. Это значит, если 25-й, 26-й, 27-й студенты имеют одинаковый средний балл, то в выборке будет не 25, а 27 записей.

Например, выбрать из таблицы R1 пять студентов с наивысшим средним баллом (средний балл вычисляется на основе поля Оценка, в выборке вычисляемое поле названо Средний балл с помощью конструкции AS):

SELECT TOP 5 R1.ФИО, Avg(R1.Оценка) AS [Средний балл]

FROM R1

GROUP BY R1.ФИО

ORDER BY Avg(R1.Оценка) DESC;

Можно использовать ключевое слово PERCENT для того, чтобы включить в выборку определенный процент из верхней или нижней части диапазона, отсортированного в соответствии с директивой ORDER BY.

Например, выбрать десять процентов записей выпуска 2005 года из таблицы СТУДЕНТЫ:

SELECT TOP 10 PERCENT ФИО, Шифр_группы

FROM СТУДЕНТЫ

WHERE Год_выпуска = 2005

ORDER BY Средний_балл DESC;

4.7.7Вложенные запросы

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.

В части FROM оператора SELECT допустимо применять синонимы к именам таблицы, если при формировании запроса требуется более чем один экземпляр некоторого отношения. Синонимы задаются с использованием ключевого слова AS, которое может быть вообще опущено. Поэтому часть FROM может выглядеть следующим образом:

FROM R1 AS A, R1 AS B

Или

FROM R1 A, R1 B

Оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

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

подзапросы должны заключаться в круглые скобки;

предложение ORDER BY может быть использовано только в основном запросе;

подзапросы, возвращающие записи, могут использоваться только с условием EXISTS (который определяет пуста или не пуста результирующая выборка записей).

Рассмотрим примеры вложенных запросов.

Пример 1. Сформировать список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM R1 AS A

WHERE Оценка >2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*) FROM R2,R3

WHERE R2.Группа = R3.Группа AND ФИО = A.ФИО);

Результат:

ФИО

Петров Сидоров Трофимов Иванова Уткина

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

Пример 2. Сформировать список тех, кто должен был сдавать экзамен по Моделированию, но пока еще не сдавал:

SELECT ФИО

FROM R2 AS A, R3

WHERE A.Группа = R3.Группа AND R3.Дисциплина = «Моделирование» AND EXISTS (SELECT ФИО FROM R1

WHERE ФИО=A.ФИО AND Дисциплина =

«Моделирование» AND Оценка IS NULL);

Результат: Миронов

Предикат EXISTS (SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен. Предикат NOT EXISTS обратно – истинен только тогда, когда подзапрос SubQuery пуст.

Пример 3. Пусть имеются два отношения РТ (Номер_поставщика, Номер_товара) и Т (Номер_товара, Наименование). Найти поставщиков, которые поставляют все товары.

SELECT DISTINCT [Номер_поставщика]

FROM PT AS PT1

WHERE NOT EXISTS

(SELECT Номер_товара FROM T

WHERE NOT EXISTS

(SELECT * FROM PT AS PT2

WHERE PT2.Номер_поставщика= PT1.Номер_поставщика AND PT2.Номер_товара = T.Номер_товара));

Фактически запрос был переформулирован так: «Найти поставщиков таких, что не существует детали, которую бы они не поставляли». Данный запрос может быть реализован и через агрегатные функции с подзапросом:

SELECT DISTINCT PT.Номер_поставщика,

COUNT(PT.Номер_товара) AS [COUNT- Номер_товара]

FROM PT

GROUP BY PT.Номер_поставщика

HAVING (((Count(PT.Номер_товара))=

(SELECT Count(Номер_товара) FROM T)));

Пример 4. Даны две таблицы: Товары (Код_товара, Наименование, Цена) и Продажи (Чек, Код товара, Дата, Продано). Из таблицы Товары требуется отобрать товары (указать поля Наименование и Цена), для которых поле Продано превышает 10.

SELECT Наименование, Цена FROM Товары

WHERE EXISTS (SELECT [Код товара] FROM Продажи

WHERE (Продано > 10) AND (Товары.[Код_товара]=

Продажи.[Код_товара]);

Пример 5. Выбрать из таблицы Продажи (указать поля Чек и Продано) товары с наименование «Шоколад Путешествие».

SELECT Чек, Продано FROM Продажи

WHERE [Код_товара] = (SELECT [Код_товара] FROM Товары WHERE Наименование = “Шоколад Путешествие”);

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