Главная ]
Подведение итогов, Группировка и Сортировка Результатов Запроса
Программирование
Базы данных



Подведение итогов, Группировка и Сортировка Результатов Запроса

 

В операторе выбора select можно подводить итоги (суммировать), группировать, сортировать результаты запросов с помощью агрегирующих функций, которые располагаются в конструкциях group by (группировка), having (имеющий), order by (упорядочение). В языке Transact SQL можно также использовать агрегирующие функции в конструкции compute (вычислить) для получения отчета с итоговыми строками. Оператор union (объединение) позволяет соединять результаты запросов.

 

В этой главе рассматриваются следующие темы:

 

·         Как вычислить итоговые значения, используя агрегирующие функции;

·         Как организовать группу данных из результатов запроса;

·         Как отбирать группы данных;

·         Как упорядочить результаты запроса;

·         Как вычислить итоговое значение по группам данных;

·         Как соединять результаты запросов.

 

Если ваш SQL Server не различает регистр символов, то в Справочном руководстве SQL Сервера можно посмотреть примеры зависимости возвращаемых результатов от регистра символов в конструкциях compute и group by.

 

 

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

 

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

 

В следующем примере вычисляется общая сумма, вырученная от продажи книг в текущем году:

 

select sum(total_sales)

from titles

----------------------------- 

                    97446

 

                      (Выбрана 1 строка)

 

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

 

aggregate_function ([all | distinct] выражение)

 

К агрегирующим функциям относятся sum (сумма), avg (среднее значение), max (максимум), min (минимум), count (подсчет количества) и count(*) (общее число). Опция distinct (различные), которая может использоваться в фукциях sum, avg и count, позволяет исключить дублирующие значения и вести подсчет только различных значений указанного поля данных. Эту опцию нельзя использовать в функциях max, min и count(*). Для функций sum, avg и count по умолчанию предполагается опция all (все), которая указывает на выполнение операций по всем значениям, включая дублирующиеся. Опцию all можно не указывать.

 

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

 

Например, в следующем запросе выясняется какой будет средняя цена книги, если все цены на книги предварительно удвоить:

 

select avg(price * 2)

from titles

--------------------------

29.53

 

                       (Выбрана 1 строка)

 

В следующей таблице указан синтаксис агрегирующих функций и результаты, которые они возвращают.

 

Функция

Результат

sum([all | distinct] выражение)

Общая сумма (различных) значений выражения

avg([all | distinct] выражение)

Средняя величина (различных) значений выражения

count([all | distinct] выражение)

Число (различных) отличных от нее значений выражения

count(*)

Общее число выбранных строк

max(выражение)

Максимальное значение выражения

min(выражение)

Минимальное значение выражения

 

Таблица 3-1: Синтаксис и результаты агрегирующих функций

 

Агрегирующие функции можно использовать в списке выбора, как это было показано в предыдущем примере, или в конструкции having (см. главу “Выбор Групп Данных: Конструкция having”).

 

Агрегирующие функции нельзя использовать в конструкции where (где).

 

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

 

Если оператор выбора содержит конструкцию where, но не содержит конструкцию group by (группировка), то агрегирующая функция будет выдавать одно значение для подмножества строк, отобранных конструкцией where. Однако, в расширении Transact-SQL можно также указать название столбца в списке выбора, в результате чего в каждой строке будет повторяться одно и то же итоговое значение. В этом случае результат запроса будет таким же, как и при использовании конструкции having, как это описывается в главе “Выбор Групп Данных: Конструкция having”.

 

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

 

select avg(advance), sum(total_sales)

from titles

where type = “business”

                    --------------------------       -------------------

                    6,281.25                          30788

 

                      (Выбрана 1 строка)

 

Когда агрегирующая функция используется в операторе выбора, который не содержит конструкции group by, то в результате появится одно итоговое значение независимо от наличия или отсутствия конструкции отбора where. Это называется скалярным агрегированием.

 

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

 

 

Функции sum (сумма) и avg (среднее) могут применяться только к числовым типам - int (целое), smallint (малое целое), tinyint (очень малое целое), decimal (десятичное), numeric (числовой), float (плавающий), money (денежный).

 

Функции min (минимум) и max (максимум) нельзя применять к данным типа bit (бит).

 

Агрегирующие функции, отличные от count(*), нельзя применять к данным типа text (текст) и image (графика).

 

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

 

select min(au_lname)

from authors

 

                    --------------------------

                    Bennet

 

                      (Выбрана 1 строка)

 

 

Функция count(*) (общее число) не имеет аргумента, поскольку по определению она относится ко всей таблице, а не к отдельному ее столбцу. Она используется для нахождения общего числа строк в таблице. В следующем запросе находится общее число книг, имеющихся в таблице:

 

select count(*)

from titles

                    --------------------------

                          18

 

                       (Выбрана 1 строка)

 

Функция count(*) возвращает общее число строк в таблице, включая одинаковые строки. При этом каждая строка считается отдельно, включая строки, содержащие неопределенные (пустые) значения.

 

Как и другие агрегирующие функции, функция count(*) может комбинироваться с другими агрегирующими функциями в списке выбора, с конструкцией where и т.д. Например:

 

select count(*), avg(price)

from titles

where advance > 1000

 

                   ---------------------          -------------------

                           15                          14.2

 

                     (Выбрана 1 строка)

 

 

Как уже отмечалось, опцию distinct (различные) можно использовать в функциях sum, avg и count. Ее нельзя использовать в функциях min, max и count(*). Если используется эта опция, то перед применением агрегирующей функции устраняются все дублирующиеся значения аргумента.

 

Кроме того, если используется эта опция, то аргумент не может быть арифметическим выражением. Он должен состоять только из названия столбца таблицы.

 

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

 

select count(distinct city)

from authors

--------------------------

             16

 

                       (Выбрана 1 строка)

 

Следующий оператор возвращает среднюю величину различных цен для книг по бизнесу:

 

select avg(distinct price)

from titles

where type = “business”

-------------------------------

                                11.64

 

                       (Выбрана 1 строка)

 

Если несколько книг имеют одинаковую цену и используется опция distinct, то их цена будет посчитана только один раз. Конечно, для правильного подсчета средней цены, необходимо удалить опцию distinct:

 

select avg(price)

from titles

where type = “business”

-------------------------------

             13.73

 

                      (Выбрана 1 строка)

 

 

Любое неопределенное значение, появляющееся в столбце по которому вычисляется агрегирующая функция, будет игнорироваться. Если установлена опция ansinull, SQL Сервер каждый раз будет выдавать сообщение об ошибке при появлении неопределенного значения. Более детальную информацию о команде установки опций set можно посмотреть в Справочном руководстве SQL Сервера.

 

Если все значения в столбце таблицы являются неопределенными, то функция count(column_name) возвратит ноль. Например, результат запроса на подсчет числа выданных авансов, хранящихся в таблице titles, может отличаться от числа книг, хранящихся в этой таблице, поскольку в столбце advance (аванс) могут встретиться неопределенные значения:

 

select count(advance)

from titles

--------------------------

                               16

 

                       (Выбрана 1 строка)

 

select count(titles)

from titles

--------------------------

                              18

 

                       (Выбрана 1 строка)

 

Исключением здесь является функция count(*), которая считает и строки с неопределенным значением.

 

Если ни одна строка не удовлетворяет условиям отбора, содержащимся в конструкции where, то функция count возвращает нулевое значение. Все остальные функции в этом случае возвращают неопределенное значение NULL. Ниже приводятся два примера:

 

select count(distinct title)

from titles

where type = “poetry”

-------------------------------

             0

 

                      (Выбрана 1 строка)

 

select avg(advance)

from titles

where type = “poetry”

-------------------------------

                                NULL

 

                      (Выбрана 1 строка)

 

 

Конструкция group by (группировка) используется в операторе выбора для разделения результатов на группы. Группировку можно проводить по одному или нескольким названиям столбцов, или по результат вычисления, используя числовые типы данных в выражении. В конструкции group by максимальное число названий столбцов и выражений не должно превосходить 16.

 

Примечание. Нельзя проводить группировку по столбцам типа text или image.

 

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

 

В следующем примере на векторное агрегирование вычисляется средняя величина аванса и годовая сумма продаж по каждому виду книг:

 

select type, avg(advance), sum(total_sales)

from titles

group by type

 

type

------------------          --------------    ----------

UNDECIDED                         NULL                     NULL

business                               6,281.25                  30788

mod_cook                              7,500.00                24278

popular_comp                       7,500.00                12875

psychology                            4,255.00                 9939

trad_cook                               6,333.33                  19566

 

                       (Выбрано 6 строк)

 

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

 

select avg(advance), sum(total_sales)

from titles

 

                    -------------     ------------

                     5,962.50           97466

 

                       (Выбрана 1 строка)

 

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

 

select type, advance

from titles

group by type

 

type                advance

------------         ---------------

business          5,000.00

business          5,000.00

business         10,125.00

business          5,000.00

mod_cook             0.00

mod_cook       15,000.00

UNDECIDED          NULL

popular_comp    7,000.00

popular_comp    8,000.00

popular_comp        NULL

psychology        7,000.00

psychology        2,275.00

psychology        6,000.00

psychology        2,000.00

psychology        4,000.00

trad_cook         7,000.00

trad_cook         4,000.00

trad_cook         8,000.00

 

                       (Выбрано 18 строк)

 

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

 

 

Повторим здесь полный синтаксис оператора select, чтобы посмотреть в общем контексте на конструкцию group by.

 

select [all | distinct] список_выбора

  [into  [[database.] owner.] название_таблицы]

  [from [[database.] owner.] { название_таблицы | название_вьювера

     [(index название_индекса [prefetch размер] [lru | mru] ) ] }

              [holdlock | noholdlock] [shared]

       [,[[database.] owner.] { название_таблицы | название_вьювера

     [(index название_индекса [prefetch размер] [lru | mru] ) ] }

              [ holdlock | noholdlock ] [shared] ] ... ]

  [where условия_выбора ]

  [group by [all] итоговое_выражение

         [, итоговое_выражение ] ... ]

  [having условия_поиска ]

  [order by

  { [[database.] owner.] { название_таблицы. | название_вьювера. } ]

       название_столбца | номер_списка_выбора | выражение }

                  [ask | desc]

  [, { [[database.] owner.] { название_таблицы. | название_вьювера. } ]

       название_столбца | номер_списка_выбора | выражение }

                  [ask | desc] ... ]

  [compute row_agregate (название_столбца)

                [, row_agregate (название_столбца) ] ...

           [by название_столбца [, название_столбца] ... ]]

  [for {read only | update [of список_названий_столбцов] } ]

  [at isolation {read uncommitted | read committed |

          serializable} ]

  [for browse]

 

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

 

Стандарт SQL на использование конструкции group by является более строгим по сравнению с вышеуказанным. Стандарт требует соблюдения следующих условий:

·       Названия столбцов, находящиеся в списке выбора, должны присутствовать    также в  конструкции group by или быть аргументами агрегирующих функций.

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

 

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

 

set fipsflagger on

 

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

 

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

 

select pub_id, type, avg(price), sum(total_sales)

from titles

group by pub_id, type

 

pub_id  type

------  -----------------            ------    -------

 

0736    business                     2.99   18722

0736    psychology                11.48    9564

0877    UNDECIDED             NULL    NULL

0877    mod_cook                  11.49   24278

0877    psychology                21.59      375

0877    trad_cook                   15.96   19566

1389    business                     17.31   12066

1389    popular_comp           21.48   12875

 

                       (Выбрано 8 строк)

 

Можно разбивать группы на все более мелкие подгруппы до тех пор, пока вложенность названий столбцов и выражений в конструкции group by не достигнет 16.

 

 

С целью расширения возможностей, заложенных в стандартном SQL, в языке Transact-SQL не накладывается никаких ограничений на содержание списка выбора в операторе select, который содержит конструкцию группировки:

1. Названия столбцов в списке выбора не обязаны присутствовать также в    конструкции группировки или быть аргументами агрегирующих функций.

2.  Названия столбцов в конструкции группировки не обязаны присутствовать в списке выбора.

 

Векторное агрегирование предполагает, что названия нескольких столбцов указаны в конструкции группировки. Стандарт SQL требует, чтобы все названия столбцов в списке выбора, к которым не применяются агрегирующие функции, присутствовали также в конструкции group by. Однако первое из вышеуказанных расширений позволяет указывать “дополнительные” столбцы в списке выбора запроса.

 

Например, следующий запрос, в котором введен дополнительный столбец title_id в список выбора, был бы неправильным для большинства версий SQL, но он является вполне допустимым в языке Transact-SQL:

 

select type, title_id, avg(price), avg(advance)

from titles

group by type

 

type                       title_id

-------------            -------------         --------   -----------

business                 BU1032           13.73   6,281.25

business               BU1111            13.73   6,281.25

business                 BU2075           13.73   6,281.25

business                 BU7832           13.73   6,281.25

mod_cook              MC2222          11.49   7,500.00

mod_cook              MC3021          11.49   7,500.00

UNDECIDED         MC3026          NULL   NULL

popular_comp      PC1035             21.48   7,500.00

popular_comp      PC8888             21.48   7,500.00

popular_comp       PC9999            21.48   7,500.00

psychology            PS1372            13.50   4,255.00

psychology            PS2091            13.50   4,255.00

psychology            PS2106            13.50   4,255.00

psychology            PS3333           13.50   4,255.00

psychology            PS7777            13.50   4,255.00

trad_cook               TC3218            15.96   6,333.33

trad_cook               TC4203          15.96   6,333.33

trad_cook               TC7777            15.96   6,333.33

 

                       (Выбрано 18 строк)

 

В этом примере по группам, определяемым видом (типом) книг type, вычисляется среднее значение в столбцах price и advance, но в результате дополнительно выводится номер книги в столбце title_id, поэтому среднее значение цены и аванса повторяется для всех книг из одной группы.

 

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

 

select state, count(au_id)

from authors

group by state, city

 

 

state

-----------  ---------

AU                           1

CA                           2

CA                           1

CA                           5

CA                           2

CA                           1

CA                           1

CA                           1

CA                           1

IN                             1

KS                            1

MD                          1

MI                            1

OR                           1

TN                           1

UT                           1

 

                      (Выбрано 16 строк)

 

В этом примере группировка осуществляется по штату (state) и городу (city), где проживает автор, однако из результата этого запроса не видно какие именно города участвовали в образовании групп.

 

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

 

select state, count(au_id)

from authors

group by city

 

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

 

Когда расширенные возможности языка Transact-SQL используются в сложных запросах, включающих соединение и конструкцию where, то понять их бывает еще труднее. Чтобы избежать ошибок и заблуждений при использовании конструкции group by, следует очень осторожно использовать эти расширения. Следует также установить опцию (флаг) fipsflagger, чтобы выделить запросы, использующие эти дополнительные возможности.

 

Дополнительную информацию о расширениях языка Transact-SQL, касающихся группировки,  и о том как они исполняются можно посмотреть в Справочном руководстве SQL Сервера.

 

 

 

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

 

select avg(total_sales), total_sales * price

from titles

group by total_sales * price

 

----------------    -------------------

      111              777.00 

      375            7,856.25 

      375            8,096.25

     2045           22,392.75 

     3336           26,654.64 

     2032           40,619.68 

     3876           46,318.20 

    18722           55,978.78 

     4095           61,384.05 

    22246          66,515.54 

     4072           81,399.28 

     4095           81,859.05 

     4095           81,900.00  

    15096          180,397.20 

     8780          201,501.00

 

                       (Выбрано 15 строк)

 

Нельзя группировать по заголовкам столбцов или псевдонимам (alias),  хотя псевдонимы можно использовать в списке выбора. Следующий запрос вызовет сообщение об ошибке:

 

select Category = type, title_id, avg(price), avg(advance)

from titles

group by Category /* Неправильное использование заголовка */

 

Чтобы скорректировать этот запрос, следует в конструкции group by указать название столбца type.

 

 

Еще одним расширением языка Transact-SQL является возможность вложенного агрегирования, то есть использование векторного агрегирования внутри скалярного. Например, в следующем запросе вычисляется средняя цена для каждого вида книг:

 

select avg(price)

from titles

group by type

 

----------------

NULL

13.73

11.49

21.48

13.50

15.96

 

                      (Выбрано 6 строк)

 

Но можно в одном запросе сразу найти максимальное значение средней цены по всем видам книг путем композиции агрегирующих функций avg и max:

 

select max(avg(price))

from titles

group by type

 

---------------------

21.48

 

                      (Выбрана 1 строка)

 

По определению конструкция group by применяется всегда к самой внутренней агрегирующей функции - в данном случае к функции avg.

 

 

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

 

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

 

select advance, avg(price * 2)

from titles

group by advance

 

advance                                           

------------------------      ----------------- 

             NULL              NULL 

              0.00               39.98 

         2,000.00              39.98 

         2,275.00              21.90 

         4,000.00              19.94 

         5,000.00              34.62 

         6,000.00              14.00 

         7,000.00              43.66 

         8,000.00              34.99 

        10,125.00               5.98 

        15,000.00               5.98

 

                      (Выбрано 11 строк)

 

Если используется агрегирующая функция count(название_столбца) и группировка проводится по столбцу, содержащему неопределенные значения, то для группы строк, соответствующей неопределенному значению, будет выдан в результате ноль, поскольку функция count не считает неопределенные значения. В большинстве случаев здесь для подсчета нужно использовать функцию count(*). В следующем примере проводится группировка по столбцу price из таблицы titles и для сравнения выводятся значения функций count и count(*):

 

select price, count(price), count(*)

from titles

group by price

 

price                                            

---------------   -----   -----

       NULL       0       2

         2.99        2       2

         7.00        1       1

         7.99        1       1

       10.95        1       1

       11.95        2       2

       14.99        1       1

       19.99        4       4

       20.00        1       1

       20.95        1       1

       21.59        1       1

22.95         1       1

 

                             (Выбрано 12 строк)

 

 

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

 

select type, avg(price)

from titles

where advance > 5000

group by type

 

type

--------------------               ------------

business                                2.99

mod_cook                              2.99

popular_comp                       21.48

psychology                            14.30

trad_cook                               17.97

 

                      (Выбрано 5 строк)

 

Здесь группируются только строки, для которых величина аванса (advance) превосходит $5000, и затем вычисляются значения агрегирующих функций. Результаты этого запроса будут сильно отличаться от результатов запроса, в котором отсутствует конструкция where.

 

Однако, способ, которым SQL Сервер вычисляет результаты, когда в списке выбора присутствует дополнительный столбец, на первый взгляд противоречат условиям отбора. Действительно, рассмотрим следующий пример:

 

select type, advance, avg(price)

from titles

where advance > 5000

group by type

 

type              advance

-------------      ------------     ----------

business           5,000.00       2.99

business           5,000.00       2.99

business         10,125.00       2.99

business           5,000.00       2.99

mod_cook               0.00       2.99

mod_cook      15,000.00       2.99

popular_comp  7,000.00     21.48

popular_comp  8,000.00     21.48

popular_comp      NULL     21.48

psychology       7,000.00     14.30

psychology       2,275.00     14.30

psychology       6,000.00     14.30

psychology       2,000.00     14.30

psychology       4,000.00     14.30

trad_cook         7,000.00     17.97

trad_cook         4,000.00     17.97

trad_cook         8,000.00     17.97

 

(Выбрано 17 строк)

 

Здесь кажется, что условие отбора было проигнорировано, поскольку в (дополнительном) столбце аванса появились значения, которые ему не удовлетворяют. На самом деле SQL Сервер по прежнему проводит векторное агрегирование только по строкам, удовлетворящим условию в конструкции where, но в результат выводятся все значения аванса, имеющиеся в таблице, поскольку этот столбец был указан в списке выбора. Чтобы устранить лишние строки из результата, здесь нужно использовать конструкцию having, которая будет описана далее в этой же главе.

 

Конструкция group by и опция all

 

Ключевое слово all (все) в конструкции group by является еще одним расширением языка Transact-SQL по сравнению с обычным SQL. Оно имеет смысл только в том случае, если содержащий его оператор выбора, содержит также конструкцию отбора where.

 

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

 

Это иллюстрируется следующим примером:

 

select type, avg(advance)

from titles

where advance > 1000 and advance < 10000

group by type

 

type

-------------------            ----------------------

business                               5,000.00

popular_comp                       7,500.00

psychology                            4,255.00

trad_cook                               6,333.00

 

(Выбрано 4 строки)

 

select type, avg(advance)

from titles

where advance > 1000 and advance < 10000

group by all type

 

type

-------------------     -----------------------

UNDECIDED                         NULL

business                5,000.00

mod_cook                              NULL

popular_comp                       7,500.00

psychology                            4,255.00

trad_cook                               6,333.00

 

(Выбрано 6 строк)

 

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

 

Во втором операторе в результат попали все группы, включая группу по современнной кулинарии и группу с неопределенным значением аванса (UNDECIDED), несмотря на то, что группа mod_cooking пуста. Для пустых групп SQL Сервер выводит неопределенное значение NULL в столбце результатов вычисления агрегирующей функции (в примере это средняя величина аванса).

 

 

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

 

select pub_id, count(pub_id)

from publishers

 

pub_id

-----------     ----------

0736                         3

0877                         3

1389                         3

 

                      (Выбрано 3 строки)

 

SQL Сервер рассматривает столбец данных publishers как одну группу, поэтому агрегирующая функция применяется ко всему столбцу таблицы. Результат повторяется во всех строках результирующей таблицы, поскольку в списке выбора, кроме агрегирующей функции, есть дополнительный столбец.

 

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

 

select pub_id, count(pub_id)

from publishers

where pub_id < “1000”

 

pub_id

-----------    ----------

0736                         2

0877                         2

1389                         2

 

                      (Выбрано 3 строки)

 

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

 

 

В конструкции having (имеющие) указываются условия отбора групп, подобно тому, как в конструкции where (где) указываются условия отбора строк.

 

Условия отбора, задаваемые в конструкции having аналогичны условиям, задаваемым в конструкции where за одним исключением. В условиях where нельзя использовать агрегирующих функций, в то время как в конструкции having можно. Число условий в конструкции having должно быть не больше 128.

 

Следующий оператор иллюстрирует использование конструкции having с агрегирующей функцией. Он группирует строки таблицы titles по типам книг, но удаляет группы, содержащие только одну книгу.

 

select type

from titles

group by type

having count(*) > 1

 

type

-------------------

business

mod_cook

popular_comp

psychology

trad_cook

 

                      (Выбрано 5 строк)

 

Далее приводится пример конструкции having без агрегирующей функции. В нем данные из таблицы titles группируются по типам книг и удаляются те типы, которые не начинаются с буквы “p”.

 

select type

from titles

group by type

having type like ‘p%’

 

type

-------------------

popular_comp

psychology

 

                       (Выбрано 2 строки)

 

Когда в конструкции having присутствует несколько условий, то они должны соединяться логическими операциями and (и), or (или), not (не). Например, в следующем запросе данные из таблицы titles группируются по издателям, а в результат попадают только те издатели, чей идентификационный номер больше 0800, заплатившие более $15000 общего аванса и чьи книги стоят в среднем менее $18:

 

select pub_id, sum(advance), avg(price)

from titles

group by pub_id

having sum(advance) > 15000

and avg(price) < 18

and pub_id > “0800”

 

pub_id

------------     -----------------          -------------

0877                         41,000.00                15.41

 

                      (Выбрана 1 строка)

 

 

Когда в запросе вместе присутствуют конструкции having, group by и where, то на окончательный результат влияет порядок их применения. Эти конструкции применяются в следующем порядке:

·           Сначала применяется конструкция where и отбираются строки, удовлетворящие условиям отбора;

·           Затем применяется конструкция group by и оставшиеся строки собираются в группы, каждая из которых соответствует одному значению группового выражения;

·           Затем к группам применяются агрегирующие функции, указанные в списке выбора и для каждой группы вычисляются итоговые значения;

·           Наконец, применяется конструкция having и из окончательного результата удаляются те группы, которые не удовлетворяют условиям отбора.

 

Следующий запрос иллюстрирует использование этих конструкций в одном операторе выбора:

 

select stor_id, title_id, sum(qty)

from salesdetail

where title_id like “PS%”

group by stor_id, title_id

having sum(qty) > 200

 

stor_id  title_id

-------    -------------       -----------

5023     PS1372            375

5023     PS2091           1845

5023     PS3333           3437

5023     PS7777           2206

6380     PS7777            500

7067     PS3333            345

7067     PS7777            250

 

                      (Выбрано 7 строк)

 

В этом запросе конструкция where отбирает книги, номер которых начинается с префикса “PS” (книги по психологии), затем конструкция group by группирует их по значениям данных в столбцах stor_id и title_id. Затем вычисляется общая сумма проданных книг по каждой группе и из окончательного результата с помощью конструкции having удаляются те группы, в которых объем продаж оказался меньше 200 книг.

 

Во всех вышеприведенных примерах использование конструкции having соответствует стандарту SQL, который утверждает, что названия столбцов, расположенные в конструкции having, должны присутствовать либо в списке выбора, либо в конструкции group by. Однако в языке Transact-SQL разрешается использовать в конструкции having дополнительные столбцы.

 

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

 

select type, avg(price)

from titles

group by type

having sum(total_sales) > 10000

 

type

-----------------------           ------------

business                               13.73

mod_cook                              11.49

popular_comp                       21.48

trad_cook                               15.96

 

                      (Выбрано 4 строки)

 

Это расширение равносильно тому, что дополнительный столбец или выражение как-бы является членом списка выбора, который просто не появляется в результате. Если в конструкции having дополнительный столбец задается без агрегирующей функции, то результат будет похож на ранее описанный в этой главе, когда “дополнительный” столбец явно указывался в списке выбора. Например:

 

select type, avg(price)

from titles

group by type

having total_sales > 4000

 

type

-----------------------   ------------

business                                13.73

business                                13.73

business                                13.73

mod_cook                              11.49

popular_comp                       21.48

popular_comp                       21.48

psychology                            13.50

trad_cook                               15.96

trad_cook                               15.96

 

                      (Выбрано 9 строк)

 

Однако, теперь дополнительный столбец (в данном случае total_sales) является невидимым и не появляется в результате. Поэтому число одинаковых строк по каждому виду книг будет зависеть от объемов продаж отдельных книг этого вида. Из результатов запроса видно, что имеются 3 книги по бизнесу, 1 по современной кулинарии, 2 по компьютерам, 1 по психологии и 2 по традиционной кулинарии, объем продаж которых превысил 4000.

 

Как было отмечено ранее, способ которым SQL Сервер обрабатывает дополнительный столбец создает впечатление, что при выводе результатов игнорируется условие, указанное в конструкции where. Чтобы результаты, показываемые в дополнительном столбце, соответствовали условию из конструкции where, нужно повторить это условие в конструкции having. Например:

 

select type, advance, avg(price)

from titles

where advance > 5000

group by type

having advance > 5000

 

type                                         advance

-----------------------  -------------                               ------------

business                               10,125.00                  2.99

mod_cook                              15,000.00                                2.99

popular_comp                       7,000.00                 21.48

popular_comp                       8,000.00                 21.48

psychology                            7,000.00                 14.30

psychology                            6,000.00                 14.30

trad_cook                               7,000.00                 17.97

trad_cook                               8,000.00                 17.97

 

                      (Выбрано 1 строку)

 

 

Запрос, содержащий конструкцию having, обычно содержит также и конструкцию group by. Если последняя отсутствует, то все строки, удовлетворяющие условию в конструкции where, собираются в одну группу.

 

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

 

В следующем примере конструкция having используется для дополнительного отбора тех книг из таблицы titles, у которых цена превосходит среднюю цену книг, по которым был выплачен аванс меньший $4000:

 

select title_id, advance, price

from titles

where advance < 4000

having price > avg(price)

 

title_id           advance      price

-------------      --------------   ---------

BU1032          5,000.00     19.99

BU7832          5,000.00     19.99

MC2222                 0.00     19.99

PC1035           7,000.00     22.95

PC8888           8,000.00     20.00

PS1372           7,000.00     21.59

PS3333           2,000.00     19.99

TC3218          7,000.00     20.95

 

                       (Выбрано 8 строк)

 

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

 

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

 

select pub_id, count(pub_id)

from publishers

having pub_id < “1000”

 

pub_id

-------------  ---------

0736                         3

0877                         3

 

                      (Выбрано 1 строку)

 

Дополнительную информацию об использовании конструкции having без группировки можно получить Справочном руководстве SQL Сервера.

 

 

Конструкция order by (упорядочить) позволяет расположить (рассортировать) результаты запроса в соответствии с содержимым выделенных столбцов. Выделять для сортировки можно не более 16 столбцов. Упорядочение по каждому столбцу должно быть либо возрастающим (asc), либо убывающим (desc). По умолчанию предполагается возрастающее упорядочение. В следующем запросе результаты упорядочиваются по столбцу pub_id:

 

select pub_id, type, title_id

from titles

order by pub_id

 

pub_id  type              title_id

------     ---------------     ------------

0736    business          BU2075

0736    psychology       PS2091

0736    psychology       PS2106

0736    psychology       PS3333

0736    psychology       PS7777

0877    UNDECIDED      MC3026

0877    mod_cook        MC2222

0877    mod_cook        MC3021

0877    psychology       PS1372

0877    trad_cook         TC3218

0877    trad_cook         TC4203

0877    trad_cook         TC7777

1389    business            BU1032

1389    business            BU1111

1389    business            BU7832

1389    popular_comp   PC1035

1389    popular_comp   PC8888

1389    popular_comp   PC9999

 

                       (Выбрано 1 строку)

 

Если в конструкции order by указано несколько столбцов, то проводится комбинированная сортировка. Следующий оператор упорядочивает строки из таблицы titles сначала в убывающем порядке по издателям, затем по каждому издателю книги располагаются в возрастающем порядке по типу и, наконец, книги имеющие одного издателя и один тип располагаются по номерам (также по умолчанию в возрастающем порядке). Неопределенные значения в любой группе указываются первыми.

 

select pub_id, type, title_id

from titles

order by pub_id desc, type, title_id

 

pub_id    type                    title_id

---------   -------------------       ------------

1389      business                                BU1032

1389      business                                BU1111

1389      business                                BU7832

1389      popular_comp                     PC1035

1389      popular_comp        PC8888

1389      popular_comp                        PC9999

0877      UNDECIDED                           MC3026

0877      mod_cook                                MC2222

0877      mod_cook                                MC3021

0877      psychology                              PS1372

0877      trad_cook                 TC3218

0877      trad_cook                 TC4203

0877      trad_cook                 TC7777

0736      business                                 BU2075

0736      psychology                              PS2091

0736      psychology                              PS2106

0736      psychology                              PS3333

0736      psychology                              PS7777

 

                      (Выбрано 18 строк)

 

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

 

select pub_id, type, title_id

from titles

order by 1 desc, 2, 3

 

select pub_id, type, title_id

from titles

order by 1 desc, type, 3

 

В большинстве версий SQL требуется, чтобы названия столбцов в конструкции order by брались из списка выбора. В языке Transact-SQL этого не требуется. Можно сортировать результаты предыдущего запроса по столбцу title (заголовок), хотя этого столбца нет в списке выбора.

 

Замечание: Нельзя проводить сортировку по столбцам типа text (текст) и image (графика).

 

В конструкции order by нельзя также использовать подзапросы, агрегирующие функции и выражения, содержащие константы и переменные.

 

Результаты упорядочения по данным различного типа зависят от процедур сортировки, установленных на SQL Сервере. Обычно это процедуры двоичной и словарной сортировки, в которой не учитывается регистр символов. Системная процедура SP_HELPSORT позволяет увидеть установленный на Сервере порядок сортировки. Детали можно посмотреть в разделе order by в Справочном руководстве SQL Сервера.

 

 

Конструкцию order by можно использовать для сортировки результатов группировки.

 

Напомним, что конструкцию order by нужно использовать после конструкции group by. В следующем примере находится средняя цена книг каждого типа, а затем результаты располагаются в соответствии с этими средними ценами:

 

select type, avg(price)

from titles

group by type

order by avg(price)

 

type

-------------------      -----------------

UNDECIDED                         NULL

mod_cook                              11.49

psychology                            13.50

business                               13.73

trad_cook                               15.96

popular_comp                       21.48

 

                     (Выбрано 6 строк)

 

 

Конструкция compute (вычислить) является еще одним расширением языка Transact-SQL по отношению к SQL. Она используется вместе с агрегирующими функциями для вывода отчетов, в которых отражаются итоговые значения по отдельным столбцам данных. Такие отчеты обычно подготавливаются с помощью генератора отчетов и называются отчетами с раздельными итогами (control-break), поскольку итоговые значения появляются в них между группами данных, как бы разделяя их на части.

 

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

 

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

 

Синтаксис конструкции compute имеет следующий вид:

 

compute агрегирующая_функция(название_столбца)

        [, агрегирующая_функция(название_столбца) ] ...

        [by название_столбца [, название_столбца] ...]

В конструкции compute можно использовать агрегирующие функции sum, avg, min, max и count. Функции sum и avg используются только с числовыми типами данных. В отличии от конструкции order by здесь нельзя использовать порядковые номера столбцов списка выбора вместо названия столбцов.

 

                              Замечание: Нельзя использовать столбцы типа text (текст) и image (графика) в  конструкции compute.

 

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

 

select type, sum(price), sum(advance)

from titles

group by type

 

type

-------------------      -----------------    --------------------

UNDECIDED                         NULL                     NULL

business                               54.92                         25,125.00

mod_cook                              22.98                       15,000.00

popular_comp                       42.95                       15,000.00

psychology                            67.52                       21,275.00

trad_cook                               47.89                       19,000.00

 

(Выбрано 6 строк)

 

select type, price, advance

from titles

order by type

compute sum(price), sum(advance) by type

 

type                             price           advance

-------------                   ---------------     --------------

UNDECIDED            NULL         NULL

                                   sum            sum

                                   --------------    -------------

                                   NULL          NULL

 

type                        price           advance

-------------              --------------    --------------

business                 2.99             10,125.00

business                11.95            5,000.00

business                19.99            5,000.00

business                19.99            5,000.00

                             sum            sum

                        --------------    -------------

                             54.92      25,125.00

 

type                  price           advance

-------------          ------------      ---------------

mod_cook               2.99      15,000.00

mod_cook              19.99            0.00

                       sum            sum

                      ------------      ------------

                          22.98      15,000.00

 

type                  price           advance

-------------          ------------      --------------

popular_comp         NULL           NULL

popular_comp         20.00       8,000.00

popular_comp         22.95       7,000.00

                       sum            sum

                      ------------      -------------

                          42.95      15,000.00

 

type                  price           advance

-------------          ------------      -------------

psychology              7.00       6,000.00

psychology              7.99       4,000.00

psychology             10.95       2,275.00

psychology             19.99       2,000.00

psychology             21.59       7,000.00

                       sum            sum

                      ------------      -------------

                           67.52     21,275.00

 

type                  price           advance

-------------          ------------      -------------

trad_cook              11.95       4,000.00

trad_cook              14.99       8,000.00

trad_cook              20.95       7,000.00

                       sum            sum

                      ------------      -------------

47.89        19,000.00

 

                   (Выбрано 24 строки)

Итоговые значения выводятся в отдельных строках, поэтому после вывода результатов SQL Сервер выдает информационное сообщение “Выведено 24 строки”.

 

 

В следующей таблице перечислены агрегирующие функции, которые можно использовать в конструкции compute:

 

Функция

Результат

sum

Сумма значений выражения

avg

Среднее значение выражения

max

Максимальное значение выражения

min

Минимальное значение выражения

count

Число выбранных строк

 

Таблица 3-2: Агрегирующие функции, используемые в конструкции compute

 

Из этой таблицы видно, что здесь можно использовать те же агрегирующие функции, что и в конструкции group by за исключением функции count(*). Чтобы найти итоговое значение, получаемое с помощью конструкции group by и и функции count(*), следует использовать конструкцию compute без приставки by.

 

 

В конструкции compute нужно придерживаться следующих правил:

·           При агрегации нельзя использовать ключевые слова в качестве названий  столбцов;

·           Названия столбцов в конструкции compute должны присутствовать в списке выбора;

·           Если в операторе выбора есть конструкция compute, то в нем нельзя использовать конструкцию into (в), поскольку в этом случае выводимые строки нельзя вставлять в таблицу;

·           Если в конструкции compute используется ключевое слово by, то в этом же операторе должна присутствовать конструкция order by. Кроме того, список названий столбцов, следующих после приставки by, должен быть подсписком списка конструкции order by, т.е. начинаться с того же первого столбца и следовать в том же порядке слева направо без пропусков, кончая некоторым промежуточным или последним столбцом;

        Например, пусть конструкция order by имеет вид:

        order by a,b,c

        Тогда для конструкции compute допустимо одно из следующих предложений:

 

compute агрегирующая_функция(название_столбца) by a,b,c

compute агрегирующая_функция(название_столбца) by a,b

compute агрегирующая_функция(название_столбца) by a

 

        В этом случае конструкция compute не может быть ни одним из следующих         предложений:

 

compute агрегирующая_функция(название_столбца) by b,c

compute агрегирующая_функция(название_столбца) by a,c

compute агрегирующая_функция(название_столбца) by c

 

        В конструкции order by нужно использовать название столбца или выражение,         следовательно нельзя сортировать по заголовкам столбцов (alias).

·           Ключевое слово compute можно использовать без пристаки by для подсчета общей суммы, общего числа, и т.д. В этом случае не обязательно включать конструкцию order by. Использование конструкции compute без приставки by рассматривается далее.

 

 

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

 

select type, pub_id, price

from titles

where type = "psychology"

order by type, pub_id, price

compute sum(price) by type, pub_id

 

type                         pub_id       price

-----------       ---------       --------------

psychology    0736           7.00

psychology    0736           7.99

psychology    0736          10.95

psychology    0736          19.99

                                 sum

                                 -------------

                                 45.93

 

type                         pub_id       price

 ----------       -------         --------------

 psychology    0877         21.59

                                 sum

                                 -------------

                                 21.59

 

(Выбрано 7 строк)

 

 

Можно проводить агрегацию разных уровней в одном операторе, используя несколько конструкций compute. Следующий запрос очень похож на предыдущий. Отличие состоит в том, что здесь вычисляется также общая сумма цен всех книг по психологии, а не только суммы по каждому издателю:

 

select type, pub_id, price

from titles

where type = "psychology"

order by type, pub_id, price

compute sum(price) by type, pub_id

compute sum(price) by type

 

type                         pub_id       price

-----------       ---------       --------------

psychology    0736           7.00

psychology    0736           7.99

psychology    0736          10.95

psychology    0736          19.99

                                 sum

                                 -------------

                                 45.93

 

type                         pub_id       price

 ----------       -------         --------------

 psychology    0877         21.59

                                 sum

                                 -------------

                                 21.59

                                 sum

                                 -------------

                                 67.52

 

(Выбрано 8 строк)

 

 

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

 

select type, price, advance

from titles

where type like "%cook"

order by type

compute sum(price), sum(advance) by type

 

type                    price        advance

---------                 -----------    ---------------

mod_cook              2.99       15,000.00

mod_cook             19.99             0.00

                         sum          sum

                         -----------    ---------------

                          22.98       15,000.00

type                    price        advance

---------                 -----------    ---------------

trad_cook             11.95         4,000.00

trad_cook             14.99         8,000.00

trad_cook             20.95         7,000.00

                         sum          sum

                         -----------     ---------------

                         47.89        19,000.00

 

(Выбрано 7 строк)

 

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

 

 

Можно использовать различные агрегирующие функции в одной конструкции compute.

 

select type, pub_id, price

from titles

where type like "%cook"

order by type, pub_id

compute sum(price), max(pub_id) by type

 

type           pub_id          price

-----------      -------            --------------

mod_cook    0877             2.99

mod_cook    0877            19.99

                                   sum

                                   --------------

                                  22.98

                 max

                 -----

                0877

type          pub_id         price

-----------     -------            --------------

trad_cook   0877            11.95

trad_cook   0877            14.99

trad_cook   0877            20.95

                                 sum

                                  --------------

                                 47.89

               max

               -----

               0877

 

(Выбрано 7 строк)

 

 

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

 

С помощью следующего оператора вычисляются общие суммы цен и авансов для всех типов книг, цена которых превышает $20:

 

select type, price, advance

from titles

where price > $20

compute sum(price), sum(advance)

 

type                        price       advance

------------                  ---------    -------------

popular_comp            22.95      7,000.00

psychology                21.59      7,000.00

trad_cook                 20.95      7,000.00

                             sum         sum

                            =====   ====== 

                             65.49     21,000.00

 

(Выбрано 4 строки)

 

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

 

select type, price, advance

from titles

where type like "%cook"

order by type

compute sum(price), sum(advance) by type

compute sum(price), sum(advance)

 

type                    price            advance

-----------               -----------------    ------------

mod_ cook                 2.99      15,000.00

mod_cook                19.99             0.00

                         sum             sum

                         -----------------    ------------

                             22.98      15,000.00

 

type                    price            advance

-----------               -----------------  ------------

trad_cook                11.95        4,000.00

trad_cook                14.99        8,000.00

trad_cook                20.95        7,000.00

                         sum             sum

                         ---------------    ------------

                            47.89       19,000.00

                         sum             sum

                        =======  ========

                            70.87       34,000.00

 

(Выбрано 8 строк)

 

 

Команда union (объединить) языка Transact-SQL позволяет объединить результаты нескольких запросов в одно результирующее множество. Этот оператор имеет следующий синтаксис:

 

подзапрос1

[union [all] подзапросN] ...

[конструкция order by]

[конструкция compute]

 

где подзапрос1 имеет вид:

 

select список_выбора

[конструкция into]

[конструкция from]

[конструкция where]

[конструкция group by]

[конструкция having]

 

а подзапросN  имеет следующий вид:

 

select список_выбора

[конструкция from]

[конструкция where]

[конструкция group by]

[конструкция having]

 

Например, предположим, что имеются две следующих таблицы T1 и T2:

 

 

Table T1

 

 

Table T2

 

a

char(4)

b

int

 

a

char(4)

b

int

abc

def

ghi

1

2

3

 

ghi

jkl

mno

3

4

5

 

Рис. 3.1.

 

В следующем запросе строится объединение этих двух таблиц:

 

select * from T1

union

select * from T2

 

Результаты этого запроса показаны в следующей таблице:

 

Table

 

 

a

char(4)

b

int

 

abc

def

ghi

jkl

mno

1

2

3

4

5

 

 

Заметим, что по умолчанию команда union удаляет дублирующиеся строки из результатов. Если указывается опция all (все), то в результат включаются все строки, в том числе и  дублирующиеся. Заметим также, что названия столбцов для результирующей таблицы берутся из таблицы T1. В оператор языка Transact-SQL можно включать любое число команд union. Например,

 

x  union  y  union  z

 

По умолчанию SQL-Сервер обрабатывает команды union слева направо. Необходимо использовать скобки, чтобы указать другой порядок объединения. Например, следующие выражения:

 

x  union  all (y  union  z)

 

и

 

(x  union  all y)  union  z

 

не являются эквивалентными. В первом случае, дублирующиеся строки в таблицах y и z будут удалены в процессе объединения, а затем произойдет объединение x с результирующей таблицей, в которой дублирующиеся строки будут сохранены. Во втором случае, сначала будут объединены таблицы x и y с сохранением дублирующихся строк, а затем результирующее множество будет объединено с z без дублирования, поэтому в этом случае опция all не окажет влияния на окончательный результат.

 

 

Следует руководствоваться следующими правилами при использовании операторов с union:

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

 

select stor_id, date, ord_num from stores

union

select stor_id, ord_num from stores_east

 

·           Соответсвующие столбцы во всех таблицах должны быть однотипными, или должна иметься возможность неявного преобразования двух типов друг к другу, или должно быть явно указано преобразование типов. Например, объединение невозможно между столбцом типа char (символьный) и столбцом одного из числовых типов int (целый), если не указано явное преобразование типов. Однако, объединение возможно между столбцом типа money (деньги) и столбцом числового типа int. Более детальную информацию о преобразовании типов и операторе union можно получить в разделе “Функции преобразования типов” Справочного руководства SQL Сервера.

·           Соответствующие столбцы в отдельных запросах оператора union должны следовать в одинаковом порядке, поскольку оператор union соединяет данные из столбцов именно в том порядке, в каком они указаны в отдельных запросах. Например, предположим, что у нас имеется две следующих таблицы T3 и T4:

 

Table T3

 

 

 

Table T4

 

a

int

b

char(4)

c

char(4)

 

a

char(4)

b

int

1

2

3

abc

def

ghi

jkl

mno

pqr

 

abc

def

ghi

1

2

3

 

        Рис. 3.2.

 

                                Тогда запрос:

 

select a, b from T3

union

select b, a from T4

 

                               приведет к следующему результату:

 

a

b

1

2

3

abc

def

ghi

 

                        В то же время следующий запрос:

 

select a, b from T3

union

select a, b from T4

 

вызовет сообщение об ошибке, поскольку соответствующие столбцы имеют различный тип. Когда в операторе union объединяются данные различных, но совместимых типов, таких как float (плавающий) и int (целый), то они преобразуются к типу имеющему наибольшую точность.

 

·           Названия столбцов в таблице, полученной после выполнения команды объединения, берутся из первого подзапроса оператора union. Следовательно, если необходимо переименовать столбец объединенной таблицы, то это нужно сделать в первом подзапросе. Кроме того, если необходимо использовать новое название столбца в объединенной таблице, например в конструкции order by, то новое название должно быть введено в первом операторе выбора. Например, следующий запрос является правильным:

 

select Cities = city from stores

union

select city from authors

order by Cities

 

Использование union с другими командами языка Transact-SQL

 

При использовании оператора union с другими командами языка Transact-SQL следует руководствоваться следующими правилами:

·           Первый подзапрос в операторе union может содержать конструкцию into (в), которая создает таблицу, содержащую результирующее множество данных. Например, следующий оператор создает таблицу под названием results, которая является объединением таблиц publishers, stores и salesdetail:

       

        select pub_id, pub_name, city into results from publishers

        union

        select stor_id, store_name, city from stores

        union

        select stor_id, title_id, ord_num from salesdetail

 

Конструкция into может использоваться только в первом подзапросе. Если она расположена в другом месте, то появится сообщение об ошибке.

·           Конструкции order by и compute  могут использоваться только в конце оператора union для определия порядка расположения окончательных результатов или вычисления итоговых значений. Их нельзя использовать в отдельных подзапросах, составляющих оператор union.

·           Конструкции group by и having могут использоваться только в отдельных подзапросах. Их нельзя использовать для результирующего множества.

·           Команду union можно использовать также в операторе insert (вставить). Например:

 

       insert into tour

                    select city, state from stores

                    union

                    select city, state from authors

 

·            Команду union нельзя использовать в операторе creat view (создать вьювер).

·           Конструкцию for browse (для просмотра) нельзя использовать в операторах, содержащих команду union.

 

<< Назад ] Содержание ] Далее >> ]

 

 
Дизайн: Piton Alien
Rambler's Top100 Рейтинг@Mail.ru
Сайт создан в системе uCoz