Изменение порядка выводимых строк (ORDER BY)

 

Предложение ORDER BY записывается в конце SQL-запроса и применяется для упорядочивания выводимых строк. Порядок строк определяется значениями полей, имена которых записаны после слов ORDER BY. По умолчанию упорядочивание выполняется по возрастанию. При необходимости способ упорядочивания можно задать явно опциями ASC (по возрастанию) и DESC (по убыванию).

Разрешается задавать порядок выводимых строк по нескольким полям, причём сначала упорядочивание выполняется по первому указанному полю, затем в пределах упорядоченной последовательности строк – по второму и т.д.

Способ упорядочивания ASC или DESC задаётся для каждого поля отдельно.

 

Пример 19. Получить список сотрудников в порядке убывания зарплат:

SELECT LastName,FirstName,Salary FROM employee

ORDER BY Salary DESC

 

Пример 20. Получить список сотрудников с расположением фамилий по алфавиту:

SELECT LastName,FirstName,Salary FROM employee

ORDER BY LastName

 

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

SELECT Continent,Name,Area,Population FROM country

ORDER BY Continent ASC, Population DESC

 

Устранение дублирования

 

В таблицах реляционных баз данных по определению нет одинаковых строк. Однако при выполнении запросов часто выводится только часть полей и в результирующем наборе появляются строки с одинаковыми значениями. Для устранения дублирования служит модификатор DISTINCT. Этот модификатор указывается один раз в списке выбираемых элементов и действует на весь список.

Рисунок 10 - Результат запроса без дублирования категорий

 

 

Рисунок 11 - Результат запроса с дублированием категорий

 

Пример 22. По таблице parts получить список изделий:

SELECT DISTINCT Description FROM parts

 

Пример 23. По таблице biolife вывести перечень категорий, к которым относятся рыбы длиной более 70 см. Перечень упорядочить по алфавиту:

SELECT DISTINCT Category FROM biolife

WHERE biolife."Length (cm)">70 ORDER BY Category

 

 

Вычисления в запросах

Вычисляемые поля

При выводе данных из таблиц можно использовать значения полей для выполнения вычислений. Для создания вычисляемого поля надо в команде SELECT в списке выводимых значений записать выражение, которое будет вычисляться при выводе результатов запроса. Например, для получения из таблицы employee списка сотрудников с указанием зарплаты и зарплаты, увеличенной на 17%, достаточно выполнить запрос:

SELECT LastName,FirstName,Salary,Salary*1.17 FROM employee

 

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

При выводе результатов столбцу, содержащему вычисленные значения, автоматически даётся имя, построенное по введённому выражению. Язык SQL позволяет явным образом задать имя столбца с помощью фразы AS.

 

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

SELECT Description,OnHand*ListPrice+OnOrder*ListPrice

FROM parts

 

SELECT Description,(OnHand+OnOrder)*ListPrice FROM parts

 

Оба запроса решают поставленную задачу. Во втором запросе для задания нужной последовательности вычислений в выражении использованы скобки.

 

SELECT Description,(OnHand+OnOrder)*ListPrice AS TotalCost

FROM parts

 

В последнем варианте запроса вычисляемому полю присвоено имя.

 

 

 

Итоговые функции

 

Итоговые (агрегатные) функции предназначены для получения обобщающих сведений о результирующем наборе записей. К итоговым функциям относятся:

· SUM – вычисление суммы значений по заданному полю;

· MAX – определение максимального значения поля;

· MIN – определение минимального значения поля;

· AVG – вычисление арифметического среднего указанного поля (сумма

· значений, делённая на их количество);

· COUNT – определение количества записей в выходном наборе.

 

Итоговые функции оперируют со значениями в указанном поле таблицы и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям, а функции SUM и AVG могут применяться только к числовым полям.

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

 

Пример 25. Определить количество записей, максимальную, минимальную и среднюю длину рыбы:

SELECT count(*) AS Number,max(Length_In) AS Max_Length,

min(Length_In) AS min_Length,avg(Length_In) AS avg_Length

FROM biolife

 

Итоговые функции используются в списке предложения SELECT и в составе предложения HAVING. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то поля могут быть только аргументами итоговых функций.

 

Рисунок 12 - Применение итоговых функций

 

 

Выполнение группировки

 

Предложение GROUP BY предназначено для подведения промежуточных итогов в запросах. Для каждой отдельной группы создаётся единственная итоговая строка. При наличии в команде SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

Все имена полей, приведённые в списке предложения SELECT, должны присутствовать и в фразе GROUP BY, за исключением случаев, когда имя столбца используется в итоговой функции. При этом в предложении GROUP BY могут быть поля, отсутствующие в списке предложения SELECT.

 

Пример 26. Вывести сведения о категории, количестве записей, максимальной, минимальной и средней длины рыбы в каждой категории:

SELECT Category, count(*)AS Number,

max(Length_In) AS Max_Length,min(Length_In) AS min_Length,

avg(Length_In) AS avg_Length

FROM biolife GROUP BY Category

 

Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группировка применяется только к тем строкам, которые удовлетворяют условию поиска.

 

Пример 27. Вывести сведения о количестве и средней стоимости заказов для заказчиков с номерами более 6800:

SELECT CustNo, count(*)AS Number, avg(ItemsTotal) FROM orders

WHERE CustNo>6800

GROUP BY CustNo

 

Рисунок 13 - Применение группировки

 

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

 

Предложение HAVING

 

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

Условия в HAVING отличаются от условий в WHERE. Предложение HAVING позволяет исключить из результирующего набора группы с итоговыми значениями, которые не удовлетворяют поставленным условиям. В предложении WHERE нельзя использовать итоговые функции.

 

Пример 28. Вывести сведения о категориях (количестве записей, максимальной, минимальной и средней длине), в которых количество представителей не меньше двух:

SELECT Category, count(Category) AS Number,

max(Length_In) AS Max_Length, min(Length_In) AS min_Length,

avg(Length_In) AS avg_Length

FROM biolife

GROUP BY Category

HAVING count(Category)>=2

 

Рисунок 14 - Результат применения предложения HAVING

Пример выполнения запроса в Delphi:

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

Разместим на форме компоненты Query, DataSource, DBGrid, DBNavigator и кнопку закрытия программы, свяжем между собой компоненты Query, DataSource, DBGrid, DBNavigator (рисунок 15).

 

Рисунок 15 – Начальное размещение компонентов на форме

 

Присвоим компоненту Query1 имени QueryMain, компоненту DataSource1 – DataSourceMain. Для создания запроса откроем. Для компонента QueryMain установим свойство DatabaseName = DBDEMOS.

 

Рисунок 16 – Открытие окна утилиты SQL Explorer для создания SQL-запроса

 

Для выполнения задания нам нужны будут три таблицы:

1) Orders.db – таблица заказов. Основная таблица нашего приложения. Первичным ключем в ней является поле OrderNo. В ней имеются такие поля, как CustNo и EmpNo. CustNo – номер поставщика из таблицы поставщиков. EmpNo – номер заказчика из таблицы заказчиков.

2) Customer.db – таблица поставщиков. Является вспомогательной таблицей. Из нее нам необходимо будет получить только название компании. Ключевое поле – CustNo.

3) Employee.db – таблица заказчиков. Является вспомогательной таблицей. Из нее нам необходимо будет получить фамилию и имя заказчика.

 

В утилите SQL Explorer добавим все 3 таблицы, выбрав из выпадающего списка таблиц (рисунок 17).

 

Рисунок 17 - Добавление таблиц в SQL Explorer

 

Соединим таблицу Employee (заказчики) и таблицу Orders (заказы) по полю EmpNo. Для этого перетащим из таблицы Employee в таблицу Orders поле EmpNo. При этом если будет выбрана данная связь для просмотра, то в таблице Orders и Employee поле EmpNo будет выделено жирным шрифтом.

Также соединим таблицу Customer (поставщики) и таблицу Orders (заказы) по полю CustNo. Для этого перетащим из таблицы Customer в таблицу Orders поле CustNo. При этом если будет выбрана данная связь для просмотра, то в таблице Orders и Customer поле CustNo будет выделено жирным шрифтом.

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

Переместим поля Customer, Firstname, Lastname в начало запроса после поля OrderNo. Для этого выберем вкладку Selection и переместим поочередно каждое поле (рисунок 18). Проверим выполнение запроса, нажав кнопку . Результат выполнения запроса показан на рисунке 19.

Рисунок 18 – Установка порядка выборки столбцов в запросе

 

Рисунок 19 – Результат выполнения запроса

 

Далее необходимо скопировать сформированный SQL-запрос. Для этого необходимо в утилите SQL Builder нажать кнопку (Show and Edit SQL) или в меню выбрать пункт QueryàShow SQL (F7).

 

Рисунок 20 – Окно редактирования SQL-запроса

Закроем окно утилиты SQL Explorer. На вопрос сохранения запроса необходимо ответить утвердительно. Текст запроса автоматически сохранится в свойстве SQL компонента QueryMain.

Другим вариантом добавления SQL-запроса является ручное добавление. Для этого скопируем сформированный SQL-запрос и закроем окно редактирования SQL-запроса и окно утилиты SQL Explorer.

В проекте Delphi выберем компонент QueryMain и откроем свойство SQL. Появится окно (рисунок 21), в которое необходимо вставить сформированный SQL-запрос.

 

Рисунок 21 – добавление SQL-запроса в компонент QueryMain

 

Переведя в активное состояние компонент QueryMain, мы увидим список заказов, в который были добавлены название организации – поставщика, а также фамилия и имя заказчика.