Глава 8. ФОРМИРОВАНИЕ ВЫВОДА ЗАПРОСОВ

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

СТРОКИ И ВЫРАЖЕНИЯ

Большинство основанных на 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;

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

           ===============  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: Комбинация текста, значений поля, и агрегатов

мы будем рассматривать в Главе 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
                 ORDER BY 2 DESC;


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

	       Рисунок 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.)