Подведение итогов, группировка и сортировка
01.01.2007
В операторе выбора 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(*)
Функция count(*) (общее число) не имеет аргумента, поскольку по определению она относится ко всей таблице, а не к отдельному ее столбцу. Она используется для нахождения общего числа строк в таблице. В следующем запросе находится общее число книг, имеющихся в таблице:
select count(*)
from titles
--------------------------
18
(Выбрана 1 строка)
Функция count(*) возвращает общее число строк в таблице, включая одинаковые строки. При этом каждая строка считается отдельно, включая строки, содержащие неопределенные (пустые) значения.
Как и другие агрегирующие функции, функция count(*) может комбинироваться с другими агрегирующими функциями в списке выбора, с конструкцией where и т.д. Например:
select count(*), avg(price)
from titles
where advance > 1000
--------------------- -------------------
15 14.2
(Выбрана 1 строка)
Использование агрегирующих функций с опцией distinct
Как уже отмечалось, опцию 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 (группировка) используется в операторе выбора для разделения результатов на группы. Группировку можно проводить по одному или нескольким названиям столбцов, или по результат вычисления, используя числовые типы данных в выражении. В конструкции 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, выдаются результаты для каждой строки таблицы.
Синтаксис конструкции group by
Повторим здесь полный синтаксис оператора 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.
Ссылка на другие столбцы в запросах с использованием group by
С целью расширения возможностей, заложенных в стандартном SQL, в языке Transact-SQL не накладывается никаких ограничений на содержание списка выбора в операторе select, который содержит конструкцию группировки:
Названия столбцов в списке выбора не обязаны присутствовать также в конструкции группировки или быть аргументами агрегирующих функций.
Названия столбцов в конструкции группировки не обязаны присутствовать в списке выбора.
Векторное агрегирование предполагает, что названия нескольких столбцов указаны в конструкции группировки. Стандарт 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 Сервера.
Выражения и конструкция group by
Другой дополнительной возможностью языка 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.
Неопределенные значения и конструкция group by
Если столбец, по которому проводится группировка, содержит неопределенные значения, то все строки содержащие это значение (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 и group by
В операторе, содержащем конструкцию группировки, можно также использовать конструкцию отбора 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
В конструкции 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
Когда в запросе вместе присутствуют конструкции 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 без группировки
Запрос, содержащий конструкцию 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
Конструкция 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 и group by
Конструкцию 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
Конструкция 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
В следующей таблице перечислены агрегирующие функции, которые можно использовать в конструкции compute:
| Функция | Результат |
|---|---|
| sum | Сумма значений выражения |
| avg | Среднее значение выражения |
| max | Максимальное значение выражения |
| min | Минимальное значение выражения |
| count | Число выбранных строк |
Таблица 3-2: Агрегирующие функции, используемые в конструкции compute
Из этой таблицы видно, что здесь можно использовать те же агрегирующие функции, что и в конструкции group by за исключением функции count(). Чтобы найти итоговое значение, получаемое с помощью конструкции group by и и функции count(), следует использовать конструкцию compute без приставки by.
Правила для конструкции compute
В конструкции 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 рассматривается далее.
Указание нескольких столбцов в конструкции 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
Можно проводить агрегацию разных уровней в одном операторе, используя несколько конструкций 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
Можно использовать различные агрегирующие функции в одной конструкции 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
Ключевое слово 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
Команда 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 | 1 | | ghi | 3 |
| def | 2 | | jkl | 4 |
| ghi | 3 | | mno | 5 |
+-------------+-------------+ +-------------+-------------+
Рис. 3.1.
В следующем запросе строится объединение этих двух таблиц:
select * from T1
union
select * from T2
Результаты этого запроса показаны в следующей таблице:
+-----------------------+-----------------------+
| Table |
+-----------------------+-----------------------+
| a char(4) | b int |
+-----------------------+-----------------------+
| abc | 1 |
| | |
| def | 2 |
| | |
| ghi | 3 |
| | |
| jkl | 4 |
| | |
| mno | 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:
Списки выбора в операторе 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 | abc | jkl | | abc | 1 | | 2 | def | mno | | def | 2 | | 3 | ghi | pqr | | ghi | 3 | +----------+-----------+-----------+ +-----------+-------+ Рис. 3.2.Тогда запрос:
select a, b from T3 union select b, a from T4приведет к следующему результату:
+-------------------------------+---------------------------+ | a | b | +-------------------------------+---------------------------+ | 1 | abc | | 2 | def | | 3 | 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.