к алгоритмизации к оглавлению Описание SQL

Мартин Грубер

Понимание SQL

(перевод Лебедева В.Н., под редакцией Булычева В.Н.)

Обобщение данных с помощью агрегатных функций

Что такое агрегатные функции?
Как использовать агрегатные функции?
Специальные атрибуты COUNT
Использование DISTINCT
Использование COUNT со строками
Включение дубликатов в агрегатные функции
Агрегаты, построенные на скалярном выражении
Предложение GROUP BY
Предложение HAVING
Не делайте вложенных агрегатов
Резюме
Работа с SQL
Форматирование вывода запросов
Строки и выражения
Скалярное выражение на основе выбираемых полей
Столбцы вывода
Помещение текста в вашем выводе запроса
Упорядочение вывода полей
Упорядочение по нескольким столбцам
Упорядочение агрегатных групп
Упорядочение вывода по номеру столбца
Упорядочение с помощью оператора NULL
Резюме
Работа с SQL

В ЭТОЙ ГЛАВЕ ВЫ ПЕРЕЙДЕТЕ ОТ ПРОСТОГО использования запросов к извлечению значений из базы данных и определению, как вы можете использовать эти значения, чтобы получить из них информацию. Это делается с помощью агрегатных или общих функций, которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете, как использовать эти функции, как определить группы значений, к которым они будут применяться, и как определить, какие группы выбираются для вывода. Вы будете также видеть, при каких условиях вы сможете объединить значения поля с этой полученной информацией в одиночном запросе.

Что такое агрегатные функции?

Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:

COUNT производит номера строк или не NULL значения полей, которые выбрал запрос.

SUM производит арифметическую сумму всех выбранных значений данного поля.

AVG производит усреднение всех выбранных значений данного поля.

MAX производит наибольшее из всех выбранных значений данного поля.

MIN производит наименьшее из всех выбранных значений данного поля.

Как использовать агрегатные функции?

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением, они берут имена поля как аргументы. Только числовые поля могут использоваться с SUM и AVG.

С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке (выдача алфавитного упорядочения обсуждается более подробно в Главе 4).

Чтобы найти SUM всех наших покупок в таблицы Заказов, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1:

SELECT SUM (amt)
FROM Orders;

=============== SQL Execution Log ============
| SELECT SUM (amt) |
| FROM Orders; |
| ==============================================|
| |
| ------- |
| 26658.4 |
===============================================

Рисунок 6.1. Выбор суммы.

Это, конечно, отличается от выбора поля, при котором возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Из-за этого, агрегатные функции и поля не могут выбираться одновременно, пока не будет использовано предложение GROUP BY (описанное далее). Нахождение усредненной суммы — это похожая операция (вывод следующего запроса показывается в Рисунке 6.2):

SELECT AVG (amt)
FROM Orders;

=============== SQL Execution Log ============
| SELECT AVG (amt) |
| FROM Orders; |
| ==============================================|
| |
| ------- |
| 2665.84 |
===============================================

Рисунок 6.2. Выбор среднего.

Специальные атрибуты COUNT

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT, чтобы производить счет чисел различных значений в данном поле. Мы могли бы использовать ее, например, чтобы сосчитать номера продавцов в настоящее время описанных в таблице Заказов (вывод показывается в Рисунке 6.3):

SELECT COUNT (DISTINCT snum)
FROM Orders;

Использование DISTINCT

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования.

=============== SQL Execution Log ============
| SELECT COUNT (DISTINCT snum) |
| FROM Orders; |
| ==============================================|
| |
| ------- |
| 5 |
===============================================

Рисунок 6.3: Подсчет значений поля

Вы можете выбирать многочисленные счета (COUNT) из полей с помощью DISTINCT в одиночном запросе который, как мы видели в Главе 3, не выполнялся когда вы выбирали строки с помощью DISTINCT. DISTINCT может использоваться, таким образом, с любой функцией агрегата, но наиболее часто он используется с COUNT. С MAX и MIN это просто не будет иметь никакого эффекта, а SUM и AVG вы обычно применяете для включения повторяемых значений, так как они законно эффективнее общих и средних значений всех столбцов.

Использование COUNT со строками, а не значениями

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля, как, например, в следующем примере, вывод из которого показан на Рисунке 6.4:

SELECT COUNT (*)
FROM Customers

=============== SQL Execution Log ============
| SELECT COUNT (*) |
| FROM Customers; |
| ==============================================|
| |
| ------- |
| 7 |
===============================================

Рисунок 6.4. Подсчет строк вместо значений

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

Включение дубликатов в агрегатные функции

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

Различия между ALL и * когда они используются с COUNT:

ALL использует имя поля как аргумент.

ALL не может подсчитать значения NULL.

Пока * является единственным аргументом, который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает (COUNT) число не NULL значений в поле rating в таблице Заказчиков (включая повторения):

SELECT COUNT (ALL rating)
FROM Customers;

Агрегаты, построенные на скалярном выражении

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

SELECT MAX (blnc + amt)
FROM Orders;

Для каждой строки таблицы этот запрос будет складывать blnc и amt для этого заказчика и выбирать самое большое значение, которое он найдет. Конечно, пока заказчики могут иметь многочисленные Заказы, их неуплаченный баланс оценивается отдельно для каждого Заказа. Возможно, заказ с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе, старый баланс должен быть выбран, как в запросе выше.

Фактически, имеются большое количество ситуаций в SQL, где вы можете использовать скалярные выражения с полями или вместо полей, как вы увидите это в Главе 7.

Предложение GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, вы хотите найти наибольшую сумму приобретений, полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Заказов для каждого значения поля snum. GROUP BY, однако, позволит Вам поместить их все в одну команду:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum;

Вывод для этого запроса показывается в Рисунке 6.5.

=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 1713.23 |
| 1003 75.75 |
| 1014 1309.95 |
| 1007 1098.16 |
=================================================

Рисунок 6.5. Нахождение максимальной суммы продажи у каждого продавца.

GROUP BY применяет агрегатные функции независимо от серий групп, которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением поля snum, и функция MAX применяется отдельно для каждой такой группы. Это значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость, которая позволяет агрегатам и полям объединяться таким образом. Вы можете также использовать GROUP BY с многочисленными полями. Совершенствуя вышеупомянутый пример далее, предположим, что вы хотите увидеть наибольшую сумму приобретений получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Заказов по продавцам и датам, и применить функцию MAX к каждой такой группе, подобно этому:

SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate;

Вывод для этого запроса показывается в Рисунке 6.6.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/03/1990 767.19 |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
| 1002 10/04/1990 75.75 |
| 1002 10/06/1990 1309.95 |
| 1003 10/04/1990 1713.23 |
| 1014 10/03/1990 1900.10 |
| 1007 10/03/1990 1098.16 |
=================================================

Рисунок 6.6. Нахождение наибольшей суммы приобретений на каждый день

Конечно же, пустые группы, в дни, когда текущий продавец не имел Заказов, не будут показаны в выводе.

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

Предположим, что в предыдущем примере, вы хотели бы увидеть только максимальную сумму приобретений, значение которой выше $3000.00. Вы не сможете использовать агрегатную функцию в предложении WHERE (если вы не используете подзапрос, описанный позже), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах группы строк. Это означает, что вы не сможете сделать что-нибудь подобно следующему:

SELECT snum, odate, MAX (amt)
FROM Oreders
WHERE MAX (amt) > 3000.00
GROUP BY snum, odate;

Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение HAVING. Предложение HAVING определяет критерии, используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Правильной командой будет следующая:

SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate
HAVING MAX (amt) > 3000.00;

Вывод для этого запроса показывается в Рисунке 6.7.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate |
| HAVING MAX (amt) > 3000.00; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
=================================================

Рисунок 6.7. Удаление групп агрегатных значений

Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящем из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода. Следующая команда будет запрещена:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum
HAVING odate = 10/03/1988;

Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь (и действительно имеет) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:

SELECT snum, MAX (amt)
FROM Orders
WHERE odate = 10/03/1990
GROUP BY snum;

// для Interbase:

WHERE odate = CAST('10/03/1988' AS DATE)

Вывод показывается в Рисунке 6.8.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 5160.45 |
| 1014 1900.10 |
| 1007 1098.16 |
=================================================

Рисунок 6.8: Максимальное значение суммы приобретений у каждого продавца на 3 Октября.

Поскольку поля odate нет, не может быть и выбранных полей, значение этих данных меньше, чем в некоторых других примерах. Вывод должен, вероятно, включать что-нибудь такое, что говорит: "это — самые большие Заказы на 3 Октября". В Главе 7, мы покажем, как вставлять текст в ваш вывод.

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть наибольшие Заказы для Serres и Rifkin:

SELECT snum, MAX (amt)
FROM Orders
GROUP BY snum
HAVING snum IN (1002,1007);

Вывод для этого запроса показывается в Рисунке 6.9.

=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum |
| HAVING snum IN (1002, 1007); |
| =============================================== |
| snum |
| ------ -------- |
| 1002 5160.45 |
| 1007 1098.16 |
=================================================

Рисунок 6.9. Использование HAVING с полями GROUP BY.

Не делайте вложенных агрегатов

В строгой интерпретации ANSI SQL, вы не можете использовать агрегат агрегата. Предположим, что вы хотите выяснять, в какой день имелась наибольшая сумма приобретений. Если вы попробуете сделать это,

SELECT odate, MAX (SUM (amt))
FROM Orders
GROUP BY odate;

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

Резюме

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

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

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

Теперь, когда вы стали знатоком большого количества того, как запрос производит значения, мы покажем вам, в Главе 7, некоторые вещи которые вы можете делать со значениями, которые он производит.

Работа с SQL

Напишите запрос, который сосчитал бы все суммы приобретений на 3 Октября.
Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице Заказчиков.
Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
Напишите запрос, который бы выбирал заказчиков в алфавитном Заказе, чьи имена начинаются с буквы G.
Напишите запрос, который выбрал бы высшую оценку в каждом городе.
Напишите запрос, который сосчитал бы число заказчиков, регистрирующих каждый день свои Заказы. (Если продавец имел более одного Заказа в данный день, он должен учитываться только один раз.)

(См. Приложение A для ответов.)

Форматирование вывода запросов

ЭТА ГЛАВА РАСШИРИТ ВАШИ ВОЗМОЖНОСТИ в работе с выводом, который производит запрос. Вы узнаете, как вставлять текст и константы между выбранных полей, как использовать выбранные поля в математических выражениях, чьи результаты затем становятся выводом, и как сделать, чтобы ваши значения выводились в определенном порядке. Эта последняя особенность включена, чтобы упорядочивать ваш вывод по любым столбцам, любым полученным значениям этого столбца, или по обеим.

Строки и выражения

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

Скалярное выражение на основе выбираемых полей

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

SELECT snum, sname, city, comm * 100
FROM Salespeople;

Вывод из этого запроса показывается в Рисунке 7.1.

=============== SQL Execution Log ============
| SELECT snum, sname, city, comm * 100 |
| FROM Salespeople; |
| ==============================================|
| snum sname city |
| ------ --------- ----------- --------- |
| 1001 Peel London 12.000000 |
| 1002 Serres San Jose 13.000000 |
| 1004 Motika London 11.000000 |
| 1007 Rifkin Barcelona 15.000000 |
| 1003 Axelrod New York 10.000000 |
===============================================

Рисунок 7.1. Помещение выражения в вашем запросе.

Столбцы вывода

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

Помещение текста в вашем выводе запроса

Символ 'A', когда ничего не значит сам по себе, — является константой, такой например как число 1. Вы можете вставлять константы в предложение SELECT запроса, включая и текст. Однако символьные константы, в отличие от числовых констант, не могут использоваться в выражениях. Вы можете иметь выражение 1 + 2 в вашем предложении SELECT, но вы не можете использовать выражение типа 'A' + 'B'; это приемлемо, только если мы имеем в виду что 'A' и 'B' это просто буквы, а не переменные и не символы.

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

Вы можете усовершенствовать предыдущий пример представив комиссионные как проценты со знаком процента (%). Это даст вам возможность помещать в вывод такие единицы как символы и комментарии, как, например, в следующем примере (вывод показывается в Рисунке 7.2)

SELECT snum, sname, city, ' % ', comm * 100
FROM Salespeople;

=============== SQL Execution Log ============
| SELECT snum, sname, city, '%', comm * 100 |
| FROM Salespeople; |
| ==============================================|
| snum sname city |
| ------ -------- ----------- ---- --------- |
| 1001 Peel London % 12.000000 |
| 1002 Serres San Jose % 13.000000 |
| 1004 Motika London % 11.000000 |
| 1007 Rifkin Barcelona % 15.000000 |
| 1003 Axelrod New York % 10.000000 |
===============================================

Рисунок 7.2. Вставка символов в ваш вывод.

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

SELECT ' For ', odate, ', there are ',
COUNT (DISTINCT onum), 'orders.'
FROM Orders
GROUP BY odate;

=============== SQL Execution Log ==============
| SELECT 'For', odate, ', ' there are ' , |
| COUNT (DISTINCT onum), ' orders ' |
| FROM Orders |
| GROUP BY odate; |
| =============================================== |
| odate |
| ------ ---------- --------- ------ ------- |
| For 10/03/1990 , there are 5 orders. |
| For 10/04/1990 , there are 2 orders. |
| For 10/05/1990 , there are 1 orders. |
| For 10/06/1990 , there are 2 orders. |
=================================================

Рисунок 7.3. Комбинация текста, значений поля, и агрегатов.

Грамматической некорректности вывода, на 5 Октября, невозможно избежать, не создав запроса, еще более сложного, чем этот (вы будете должны использовать два запроса с UNION, который мы будем описывать в Главе 14). Как вы можете видеть, одиночный неизменный комментарий для каждой строки таблицы может быть очень полезен, но имеет ограничения. Иногда изящнее и полезнее произвести один комментарий для всего вывода в целом, или производить свой собственный комментарии для каждой строки.

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

Упорядочение вывода полей

Как мы подчеркивали, таблицы — это неупорядоченные наборы данных, и данные, которые выходят из них, не обязательно появляются в какой-то определенной последовательности. SQL использует команду ORDER BY, чтобы позволять вам упорядочивать ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого, также как с GROUP BY, и вы можете определять возрастание (ASC) или убывание (DESC) для каждого столбца. По умолчанию установлено — возрастание. Давайте рассмотрим нашу таблицу Заказов, приводимую в порядок с помощью номера заказчика (обратите внимание на значения в cnum столбце):

SELECT *
FROM Orders
ORDER BY cnum DESC;

Вывод показывается в Рисунке 7.4.

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| ORDER BY cnum DESC; |
| =============================================== |
| onum amt odate cnum snum |
| ------ -------- ---------- ----- ----- |
| 3001 18.69 10/03/1990 2008 1007 |
| 3006 1098.16 10/03/1990 2008 1007 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 9891.88 10/06/1990 2006 1001 |
| 3007 75.75 10/04/1990 2004 1002 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3003 767.19 10/03/1990 2001 1001 |
=================================================

Рисунок 7.4. Упорядочение вывода по убыванию поля.

Упорядочение по нескольким столбцам

Мы можем также упорядочивать таблицу по другому столбцу, например, по полю amt, внутри упорядочения поля cnum. (вывод показан в Рисунке 7.5):

SELECT *
FROM Orders
ORDER BY cnum DESC, amt DESC;

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| ORDER BY cnum DESC, amt DESC; |
| =============================================== |
| onum amt odate cnum snum |
| ------ -------- ---------- ----- ----- |
| 3006 1098.16 10/03/1990 2008 1007 |
| 3001 18.69 10/03/1990 2008 1007 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3011 9891.88 10/06/1990 2006 1001 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3007 75.75 10/04/1990 2004 1002 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3003 767.19 10/03/1990 2001 1001 |
=================================================

Рисунок 7.5. Упорядочение вывода по нескольким полям.

Вы можете использовать ORDER BY таким же способом сразу с любым числом столбцов. Обратите внимание что, во всех случаях, столбцы которые упорядочиваются должны быть указаны в выборе SELECT. Это — требование ANSI, которое в большинстве, но не всегда, предписано системе. Следующая команда, например, будет запрещена:

SELECT cname, city
FROM Customers
GROUP BY cnum;

Так как поле cnum не было выбранным полем, GROUP BY не сможет найти его, чтобы использовать для упорядочения вывода. Даже если ваша система позволяет это, смысл упорядочения не будет понятен из вывода, так что включение (в предложение SELECT) всех столбцов, используемых в предложении ORDER BY, в принципе желательно.

Упорядочение агрегатных групп

ORDER BY может кроме того, использоваться с GROUP BY для упорядочения групп. Если это так, то ORDER BY всегда приходит последним. Вот — пример из последней главы с добавлением предложения ORDER BY. Перед группированием вывода порядок групп был произвольным, и мы теперь, заставим группы размещаться в последовательности:

SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate
ORDER BY snum;

Вывод показывается на Рисунке 7.6.

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate |
| ORDER BY snum ; |
| =============================================== |
| snum odate amt |
| ----- ---------- -------- |
| 1001 10/06/1990 767.19 |
| 1001 10/05/1990 4723.00 |
| 1001 10/05/1990 9891.88 |
| 1002 10/06/1990 5160.45 |
| 1002 10/04/1990 75.75 |
| 1002 10/03/1990 1309.95 |
| 1003 10/04/1990 1713.23 |
| 1004 10/03/1990 1900.10 |
| 1007 10/03/1990 1098.16 |
=================================================

Рисунок 7.6: Упорядочение с помощью группы

Так как мы не указывали на возрастание или убывание Заказа, возрастание используется по умолчанию.

Упорядочение вывода по номеру столбца

Вместо имен столбцов вы можете использовать их порядковые номера для указания поля, используемого в упорядочении вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе. Другими словами, поле упомянутое в предложении SELECT первым, для ORDER BY — это поле 1, независимо от того каким по порядку оно стоит в таблице. Например, вы можете использовать следующую команду, чтобы увидеть определенные поля таблицы Продавцов, упорядоченными в порядке убывания к наименьшему значению комиссионных (вывод показан на Рисунке 7.7):

SELECT sname, comm
FROM Salespeople
GROUP BY 2 DESC;

=============== SQL Execution Log ============
| (SELECT sname, comm |
| FROM Salespeople |
| ORDER BY 2 DESC; |
| ============================================= |
| sname comm |
| -------- -------- |
| Peel 0.17 |
| Serres 0.13 |
| Rifkin 0.15 |
===============================================

Рисунок 7.7: Упорядочение использующее номера

Одна из основных целей этой возможности ORDER BY — дать вам возможность использовать GROUP BY со столбцами вывода также как и со столбцами таблицы. Столбцы, производимые агрегатной функцией, константы или выражения в предложении SELECT запроса, абсолютно пригодны для использования с GROUP BY, если они ссылаются к ним с помощью номера. Например, давайте сосчитаем Заказы каждого из наших продавцов, и выведем результаты в порядке убывания, как показано в Рисунке 7.8:

SELECT snum, COUNT (DISTINCT onum)
FROM Orders
GROUP BY snum
ORDER BY 2 DESC;

=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum |
| ORDER BY 2 DESC; |
| =============================================== |
| snum |
| ----- ---------- |
| 1001 3 |
| 1002 3 |
| 1007 2 |
| 1003 1 |
| 1004 1 |
=================================================

Рисунок 7.8. Упорядочение с помощью столбца вывода.

В этом случае, вы должны использовать номер столбца, так как столбец вывода не имеет имени; и вы не должны использовать саму агрегатную функцию. Строго говоря, по правилам ANSI SQL, следующее не будет работать, хотя некоторые системы и пренебрегают этим требованием:

SELECT snum, COUNT (DISTINCT onum)
FROM Orders
GROUP BY snum
GROUP BY COUNT (DISTINCT onum) DESC;

Это будет отклонено большинством систем!

Упорядочение с помощью оператора NULL

Если имеются пустые значения (NULL) в поле, которое вы используете для упорядочивания вашего вывода, они могут или следовать, или предшествовать каждому другому значению в поле. Это — возможность, которую ANSI оставил для индивидуальных программ. Данная программа использует ту или иную форму.

Резюме

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

Это дает вам возможность помечать или объяснять ваш вывод различными способами.

Вы также изучили, как упорядочивать ваш вывод. Даже если таблица сама по себе остается неупорядоченной, предложение ORDER BY дает вам возможность управлять Заказом вывода строк данного запроса. Вывод запроса может быть в Заказе возрастания или убывания, и столбцы могут быть вложенными один внутрь другого.

Понятие выводимых столбцов объяснялось в этой главе. Вы теперь знаете что выводимые столбцы можно использовать, чтобы упорядочивать вывод запроса, но эти столбцы — без имени, и следовательно должны определяться их порядковым номером в предложении ORDER BY.

Теперь, когда вы увидели, что можно делать с выводом запроса основанного на одиночной таблице, настало время чтобы перейти к возможностям улучшенного запроса и узнать как сделать запрос любого числа таблиц в одной команде, определив связи между ними, как вы это обычно делали. Это будет темой Главы 8.

Работа с SQL

Предположим, что каждый продавец имеет 12% комиссионных. Напишите запрос к таблице Заказов, который мог бы вывести номер Заказа, номер продавца, и сумму комиссионных продавца для этого Заказа.

Напишите запрос к таблице Заказчиков, который мог бы найти высшую оценку в каждом городе. Вывод должен быть в такой форме:
For the city (city), the highest rating is: (rating).
Напишите запрос, который выводил бы список заказчиков в нисходящем порядке. Вывод поля оценки (rating) должен сопровождаться именем заказчика и его номером.

(См. Приложение A для ответов.)

продолжение

к алгоритмизации к оглавлению Описание SQL

Знаете ли Вы, что диаграмма последовательности, Sequence diagram - это методология объектно-ориентированного проектирования, предназначенная для моделирования взаимодействия во времени. Диаграмма последовательности позволяет отслеживать поведение взаимодействующих групп объектов.

НОВОСТИ ФОРУМА

Рыцари теории эфира