Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.
Функция SQL SUM
Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:
SELECT SUM(ИМЯ_СТОЛБЦА) ...
После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).
Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
Для получения суммы размеров всех заработных плат используем следующий запрос:
SELECT SUM(Salary) FROM Staff
Этот запрос вернёт значение 287664,63.
А теперь . В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.
Функция SQL MIN
Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.
Пример 3. База данных и таблица - те же, что и в примере 1.
Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 10505,90.
И вновь упражнение для самостоятельного решения . В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:
Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.
Функция SQL MAX
Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.
Пример 5.
Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 18352,80
Пришло время упражнения для самостоятельного решения .
Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц) .
Функция SQL AVG
Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.
Пример 7. База данных и таблица - те же, что и в предыдущих примерах.
Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Результатом будет значение 6,33
Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.
Функция SQL COUNT
Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк) таблицы.
Пример 9. База данных и таблица - те же, что и в предыдущих примерах.
Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос:
SELECT COUNT(Comm) FROM Staff
Результатом будет значение 11.
Пример 10. База данных и таблица - те же, что и в предыдущих примерах.
Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:
SELECT COUNT(*) FROM Staff
Результатом будет значение 17.
В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.
Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных.
Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.
| Category | Part | Units | Money |
| Транспорт | Автомашины | 110 | 17600 |
| Недвижимость | Квартиры | 89 | 18690 |
| Недвижимость | Дачи | 57 | 11970 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Стройматериалы | Доски | 68 | 7140 |
| Электротехника | Телевизоры | 127 | 8255 |
| Электротехника | Холодильники | 137 | 8905 |
| Стройматериалы | Регипс | 112 | 11760 |
| Досуг | Книги | 96 | 6240 |
| Недвижимость | Дома | 47 | 9870 |
| Досуг | Музыка | 117 | 7605 |
| Досуг | Игры | 41 | 2665 |
Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос.
На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов
Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.
В языке SQL переименование полей связано с использованием ключевого слова AS , которое и используется для переименования имен полей в результирующих наборах
Синтаксис:
SELECT <имя поля> AS <псевдоним> FROM …
Рассмотрим пример переименования в SQL:
Пример БД «Институт»:
Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»
✍ Решение:
Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:
Пример БД «Институт»:
Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»
✍ Решение:
| 1 2 | SELECT name, (zarplata+ premia) AS zarplata_premia FROM teachers; |
SELECT name, (zarplata+premia) AS zarplata_premia FROM teachers;
Результат:
Агрегатные функции в SQL
Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:
Все агрегатные функции возвращают единственное значение.
Функции COUNT , MIN и MAX применимы к любым типам данных.
Функции SUM и AVG используются только для числовых полей.
Между функциями COUNT(*) и COUNT() есть разница: вторая при подсчете не учитывает NULL -значения.
Важно: при работе с агрегатными функциями в SQL используется служебное слово AS
Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»
✍ Решение:
| SELECT MAX (zarplata) AS макс_зп FROM teachers; |
SELECT MAX(zarplata) AS макс_зп FROM teachers;
Результаты:
Рассмотрим более сложный пример использования агрегатных функций в sql.
✍ Решение:
Предложение GROUP BY в SQL
Оператор group by в sql обычно используется совместно с агрегатными функциями.
Агрегатные функции выполняются над всеми результирующими строками запроса. Если запрос содержит оператор GROUP BY , каждый набор строк, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно .
Рассмотрим пример с таблицей lessons:
Пример:
Важно: Таким образом, в результате использования GROUP BY все выходные строки запроса разделяются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах (т.е. агрегатные функции выполняются для каждой группы отдельно).
При этом стоит учесть, что при группировке по полю, содержащему NULL -значения, все такие записи попадут в одну группу.
Для различных типов принтеров определить их среднюю стоимость и количество (т.е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции . Результат должен выглядеть так:
Оператор Having SQL
Предложение HAVING в SQL необходимо для проверки значений, которые получены с помощью агрегатной функции после группировки
(после использования GROUP BY). Такая проверка не может содержаться в предложении WHERE
.
Пример: БД Компьютерный магазин . Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.
- Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.
SELECT R1. Дисциплина, COUNT(*)
GROUP BY R1.Дисциплина;
Результат:
SELECT R1.Дисциплина, COUNT (*)
WHERE R1. Оценка IS NOT NULL
GROUP BY R1.Дисциплина;
Результат:
не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Теория информации» будет на 1 меньше.
Аналогичный результат можно получить, если записать запрос следующим способом:
SELECT R1. Дисциплина, COUNT(R1. Оценка)
GROUP BY R1. Дисциплина;
Функция COUNT (ИМЯ АТРИБУТА) считает количество определенных значений в группе, в отличие от функции COUNT(*), которая считает количество строк в группе. Действительно, в группе с дисциплиной «Теория информации» будет 4 строки, но только 3 определенных значения атрибута «Оценка».
Правила обработки значений NULL в агрегатных функциях
Если какие-либо значения в столбце равны NULL при вычислении результата функции они исключаются.
Если все значения в столбце равны NULL , то Max Min Sum Avg = NULL , count = 0 (ноль).
Если таблица пуста, count(*) = 0 .
Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.
Правила интерпретации агрегатных функций
Агрегатные функции могут быть включены в список вывода и тогда они применяются ко всей таблице.
SELECT MAX (Оценка) from R1 даст максимальную оценку на сессии;
SELECT SUM (Оценка) from R1 даст сумму всех оценок на сеcси;
SELECT AVG(Оценка) from R1 даст среднюю оценку по всей сессии.
2; Результат: " width="640"
Обратившись снова к базе данных «Сессия» (таблицы R1), найдем количество успешно сданных экзаменов:
SELECT COUNT(*) As Сдано _ экзаменов
WHERE Оценка 2;
Результат:
Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:
SELECT R1.Дисциплина, COUNT (DISTINCT R1.Оценка)
WHERE R1. Оценка IS NOT NULL
GROUP BY R1.Дисциплина;
Результат:
Тот же самый результат получается, если исключить явное условие в части WHERE, в этом случае запрос будет им следующий вид:
SELECT R1. Дисциплина, COUNT(DISTINCT R1. Оценка)
GROUP BY R1. Дисциплина;
Функция COUNT (DISTINCT R1.Оценка) считает только определенные различные значения.
Для того чтобы и в этом случае был получен нужный результат, необходимо сделать предварительное преобразование типа данных столбца «Оценка», приведя его к действительному типу, тогда и результат вычисления среднего не будет целым числом. В этом случае запрос будет выглядеть следующим образом:
2 Group by R2. Группа, R1. Дисциплина; Здесь функция CAST() выполняет преобразование столбца «Оценка» к действительному типу данных. " width="640"
Select R2.Группа, R1.Дисциплина,Count(*) as Всего, AVG(cast(Оценка as decimal(3,1))) as Средний_балл
From R1,R2
where R1. ФИО = R2. ФИО and R1. оценка is not null
and R1. Оценка 2
Group by R2. Группа, R1. Дисциплина;
Здесь функция CAST() выполняет преобразование столбца «Оценка» к действительному типу данных.
Нельзя использовать агрегатные функции в предложении WHERE, потому что условия в этом разделе оцениваются в терминах одиночной строки, а агрегатные функции - в терминах групп строк.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
Построим запрос, который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:
1; Результат: " width="640"
SELECT R2. Группа
FROM R1,R2
WHERE R1. ФИО = R2. ФИО AND
R1.Оценка = 2
GROUP BY R2.Группа, R1.Дисциплина
HAVING count(*) 1;
Результат:
Имеем БД «Банк», состоящую из одной таблицы F, в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:
Найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM (Остаток) из таблицы для каждого филиала, но операция группировки GROUP BY, позволит поместить их все в одну команду:
SELECT Филиал , SUM( Остаток )
GROUP BY Филиал;
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, т. е. суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY , имеет по определению только одно значение на группу вывода, как и результат работы агрегатной функции.
5 000; Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT , где используется GROUP BY . Они должны иметь одно значение на группу вывода. " width="640"
Предположим, что выбрать только те филиалы, суммарные значения остатков на счетах которых превышают $5 000, а также суммарные остатки для выбранных филиалов. Чтобы вывести в результат филиалы, суммарные остатки в которых свыше $5 000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же, как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал, SUM(Остаток)
GROUP BY Филиал
HAVING SUM ( Остаток ) 5 000;
Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT , где используется GROUP BY . Они должны иметь одно значение на группу вывода.
Следующая команда будет запрещена:
SELECT Филиал,SUM(Остаток)
GROUP BY Филиал
HAVING ДатаОткрытия = 27/12/2004 ;
Поле ДатаОткрытия не может быть использовано в предложении HAVING , потому что оно может иметь больше, чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY . Имеется правильный способ сделать вышеупомянутый запрос:
SELECT Филиал,SUM(Остаток)
WHERE ДатаОткрытия = ’27/12/2004’
GROUP BY Филиал;
Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 2004 года.
Как говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически ссылки на агрегатные функции - наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:
SELECT Филиал, SUM(Остаток)
FROM F,Q
WHERE F. Филиал = Q. Филиал
GROUP BY Филиал
HAVING Филиал IN (‘Санкт-Петербург’, ‘Псков’, ‘Урюпинск’);
100 000; Если суммарный остаток более чем $100 000, то мы его увидим в результирующем отношении, в противном случае мы получим пустое отношение. " width="640"
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия отбора в части HAVING есть TRUE. В частности, если раздел HAVING присутствует в запросе, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования. Рассмотрим пример. Допустим, мы хотим вывести общую сумму остатков по всем филиалам, но только в том случае, если она более $100 000. В этом случае наш запрос не будет содержать операции группировки, но будет содержать раздел HAVING и будет выглядеть следующим образом:
SELECT SUM( Остаток )
HAVING SUM( Остаток ) 100 000;
Если суммарный остаток более чем $100 000, то мы его увидим в результирующем отношении, в противном случае мы получим пустое отношение.
Стандарт ISO содержит определение следующих пяти агрегирующих функций:
COUNT – возвращает количество значений в указанном столбце;
SUM – возвращает сумму значений в указанном столбце;
AVG – возвращает усредненное значение в указанном столбце;
MIN – возвращает минимальное значение в указанном столбце;
МАХ – возвращает максимальное значение в указанном столбце.
Все эти функции оперируют со значениями в единственном столбце таблицы и возвращают единственное значение. Функции COUNT, MIN и МАХ применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей. За исключением COUNT (*), при вычислении результатов любых функций сначала исключаются все пустые значения, после чего требуемая операция применяется только к оставшимся непустым значениям столбца. Вариант COUNT (*} является особым случаем использования функции COUNT – его назначение состоит в подсчете всех строк в таблице, независимо от того, содержатся там пустые, повторяющиеся или любые другие значения. Если до применения агрегирующей функции необходимо исключить повторяющиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Стандарт ISO допускает использование ключевого слова ALL с целью явного указания того, что исключение повторяющихся значений не требуется, хотя это ключевое слово подразумевается по умолчанию, если никакие иные определители не заданы. Ключевое слово DISTINCT не имеет смысла для функций MIN и МАХ. Однако его использование может оказывать влияние на результаты выполнения функций SUM и AVG, поэтому следует заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT в каждом запросе может быть указано не более одного раза.
Следует отметить, что агрегирующие функции могут использоваться только в списке выборки SELECT и в конструкции HAVING (см. раздел 5.3.4). Во всех других случаях применение этих функций недопустимо. Если список выборки SELECT содержит агрегирующую функцию, а в тексте запроса отсутствует конструкция GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка выборки SELECT не может включать каких-либо ссылок на столбцы, за исключением случая, когда этот столбец используется как параметр агрегирующей функции. Например, следующий запрос является некорректным:
SELECT staffNo, COUNT (salary)
FROM Staff;
Ошибка состоит в том, что в данном запросе отсутствует конструкция GROUP BY , а обращение к столбцу staffNo в списке выборки SELECT выполняется без применения агрегирующей функции.
Пример 13. Использование функции COUNT(*). Определите, сколько сдаваемых в аренду объектов имеют ставку арендной платы более 350 фунтов стерлингов в месяц,
SELECT COUNT (*) AS count
FROM PropertyForRent
WHERE rent > 350;
Ограничение на подсчет только тех сдаваемых в аренду объектов, арендная плата которых составляет более 350 фунтов стерлингов в месяц, реализуется посредством использования конструкции WHERE. Общее количество сдаваемых в аренду объектов, отвечающих указанному условию, может быть определено с помощью агрегирующей функции COUNT. Результаты выполнения запроса представлены в табл. 23.
Таблица 23
| count |
Пример 14 . Использование функции COUNT(DISTINCT). Определите, сколько различных сдаваемых в аренду объектов было осмотрено клиентами в мае 2001 года.
SELECT COUNT(DISTINCT propertyNo) AS count
FROM Viewing
И в этом случае ограничение результатов запроса анализом только тех сдаваемых в аренду объектов, которые были осмотрены в мае 2001 года, достигается посредством использования конструкции WHERE. Общее количество осмотренных объектов, удовлетворяющих указанному условию, может быть определено с помощью агрегирующей функции COUNT. Однако, поскольку один и тот же объект может быть осмотрен различными клиентами несколько раз, необходимо в определении функции указать ключевое слово DISTINCT – это позволит исключить из расчета повторяющиеся значения. Результаты выполнения запроса представлены в табл. 24.
Таблица 24
Пример 16. Использование функций MIN, MAXnAVG. Вычислите значение минимальной, максимальной и средней заработной платы.
SELECT MIN (salary) AS min, MAX (salary) AS max, AVG (salary) AS avg
FROM Staff;
В этом примере необходимо обработать сведения обо всем персонале компании, поэтому использовать конструкцию WHERE не требуется. Необходимые значения могут быть вычислены с помощью функций MIN, MAX и AVG, применяемых к столбцу salary таблицы Staff. Результаты выполнения запроса представлены в табл. 26.
Таблица 26.
Результат выполнения запроса
| min | max | avg |
| 9000.00 | 30000.00 | 17000.00 |
Группирование результатов (конструкция GROUP BY). Приведенные выше примеры сводных данных подобны итоговым строкам, обычно размещаемым в конце отчетов. В итогах все детальные данные отчета сжимаются в одну обобщающую строку. Однако очень часто в отчетах требуется формировать и промежуточные итоги. Для этой цели в операторе SELECT может указываться конструкция GROUP BY. Запрос, в котором присутствует конструкция GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная итоговая строка. Столбцы, перечисленные в конструкции GROUP BY, называются группируемыми столбцами. Стандарт ISO требует, чтобы конструкции SELECT и GROUP BY были тесно связаны между собой. При использовании в операторе SELECT конструкции GROUP BY каждый элемент списка в списке выборки SELECT должен иметь единственное значение для всей группы. Более того, конструкция SELECT может включать только следующие типы элементов:
Имена столбцов;
агрегирующие функции;
Константы;
Выражения, включающие комбинации перечисленных выше элементов.
Все имена столбцов, приведенные в списке выборки SELECT, должны присутствовать и в конструкции GROUP BY, за исключением случаев, когда имя столбца используется только в агрегирующей функции. Противоположное утверждение не всегда справедливо – в конструкции GROUP BY могут присутствовать имена столбцов, отсутствующие в списке выборки SELECT. Если совместно с конструкцией GROUP BY используется конструкция WHERE, то она обрабатывается в первую очередь, а группированию подвергаются только те строки, которые удовлетворяют условию поиска. Стандартом ISO определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значения NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.
Пример 17. Использование конструкции GROUP BY. Определите количество персонала, работающего в каждом из отделений компании, а также их суммарную заработную плату.
SELECT branchNo, COUNT {staffNo} AS count, SUM (salary) AS sum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
Нет необходимости включать имена столбцов staffNo и salary в список элементов GROUP BY, поскольку они появляются только в списке выборки SELECT с агрегирующими функциями. В то же время столбец branchNo в списке конструкции SELECT не связан с какой-либо агрегирующей функцией и по этой причине обязательно должен быть указан в конструкции GROUP BY. Результаты выполнения запроса представлены в табл. 27.
Таблица 27
Результат выполнения запроса
| branchNo | Count | Sum |
| В003 | 54000.00 | |
| В005 | 39000.00 | |
| В007 | 9000.00 |
Концептуально при обработке этого запроса выполняются следующие действия.
1. Строки таблицы Staff распределяются в группы в соответствии со значениями в столбце номера отделения компании. В пределах каждой из групп оказываются данные обо всем персонале одного из отделений компании. В нашем примере будут созданы три группы, как показано на рис. 1.
2. Для каждой из групп вычисляются общее количество строк, равное количеству работников отделения, а также сумма значений в столбце salary, которая и является интересующей нас суммой заработной платы всех работников отделения. Затем генерируется единственная итоговая строка для всей группы исходных строк.
3. Полученные строки результирующей таблицы сортируются в порядке возрастания номера отделения, указанного в столбце branchNo.
| branchNo | staffNo | Salary |
| В00З | SG37 | 12000.00 |
| В00З | SG14 | 18000.00 |
| В00З | SG5 | 24000.00 |
| В005 | SL21 | 30000.00 |
| В005 | SL41 | 9000.00 |
| В007 | SA9 | 9000.00 |
| COUNT(staffNo) | SUM(salary) |
| 54000.00 | |
| 39000.00 | |
| 9000.00 |
Рис. 1. Три группы записей, создаваемые при выполнении запроса
Стандарт SQL допускает помещение в список выборки SELECT вложенных запросов. Поэтому приведенный выше запрос можно также представить следующим образом:
SELECT branchNo, (SELECT COUNT{staffNo) AS count
FROM Staff s
WHERE s.branchNo = b.branchNo),
(SELECT SUM(salary) AS sum
FROM Staff s
WHERE s.branchNo = b.branchNo)
FROM Branch b
ORDER BY branchNo;
Но в этой версии запроса для каждого из отделений компании, описанных в таблице Branch, создаются два результата вычисления агрегирующих функций, поэтому в некоторых случаях возможно появление строк, содержащих нулевые значения.
Ограничения на выполнение группирования (конструкция HAVING). Конструкция HAVING предназначена для использования совместно с конструкцией GROUP BY для задания ограничений, указываемых с целью отбора тех групп, которые будут помещены в результирующую таблицу запроса. Хотя конструкции HAVING и WHERE имеют сходный синтаксис, их назначение различно. Конструкция WHERE предназначена для отбора отдельных строк, предназначенных для заполнения результирующей таблицы запроса, а конструкция HAVING используется для отбора групп, помещаемых в результирующую таблицу запроса. Стандарт ISO требует, чтобы имена столбцов, применяемые в конструкции HAVING, обязательно присутствовали в списке элементов GROUP BY или применялись в агрегирующих функциях. На практике условия поиска в конструкции HAVING всегда включают, по меньшей мере, одну агрегирующую функцию; в противном случае эти условия поиска должны быть помещены в конструкцию WHERE и применены для отбора отдельных строк. (Помните, что агрегирующие функции не могут использоваться в конструкции WHERE.) Конструкция HAVING не является необходимой частью языка SQL – любой запрос, написанный с использованием конструкции HAVING, может быть представлен в ином виде, без ее применения.
Пример 18. Использование конструкции HAVING. Для каждого отделения компании с численностью персонала более одного человека определите количество работающих и сумму их заработной платы.
SELECT branchNo, COUN T(staffNo) AS count, SUM (salary) AS sum
FROM Staff
GROUP BY branchNo
HAVING COUNT (staffNo) > 1
ORDER BY branchNo;
Этот пример аналогичен предыдущему, но здесь используются дополнительные ограничения, указывающие на то, что нас интересуют сведения только о тех отделениях компании, в которых работает больше одного человека. Подобное требование налагается на группы, поэтому в запросе следует использовать конструкцию HAVING. Результаты выполнения запроса представлены в табл. 28.
Таблица 28
| branchNo count sum |
| В00З 3 54000.00 |
| В005 2 39000.00 |
Подзапросы. В этом разделе мы обсудим использование законченных операторов SELECT, внедренных в тело другого оператора SELECT. Внешний (второй) оператор SELECT использует результат выполнения внутреннего (первого) оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут находиться в конструкциях WHERE и HAVING внешнего оператора SELECT – в этом случае они получают название подзапросов, или вложенных запросов. Кроме того, внутренние операторы SELECT могут использоваться в операторах INSERT, UPDATE и DELETE. Существуют три типа подзапросов.
Скалярный подзапрос возвращает значение, выбираемое из пересечения одного столбца с одной строкой, т.е. единственное значение. В принципе скалярный подзапрос может использоваться везде, где требуется указать единственное значение. Варианты скалярных подзапросов приведены в примерах 13 и 14.
Строковый подзапрос возвращает значения нескольких столбцов таблицы, но в виде единственной строки. Строковый подзапрос может использоваться везде, где применяется конструктор строковых значений, – обычно это предикаты. Вариант строкового подзапроса приведен в примере 15.
Табличный подзапрос возвращает значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке. Табличный подзапрос может использоваться везде, где допускается указывать таблицу, например как операнд предиката IN.
Пример 19. Использование подзапроса с проверкой на равенство. Составьте список персонала, работающею в отделении компании, расположенном по адресу 463 Main St1.
SELECT
FROM Staff
WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = "163 Main S t " } ;
Внутренний оператор SELECT (SELECT branchNo FROM Branch ...) предназначен для определения номера отделения компании, расположенного по адресу "163 Main St". (Существует только одно такое отделение компании, поэтому данный пример является примером скалярного подзапроса.) После получения номера требуемого отделения выполняется внешний подзапрос, предназначенный для выборки подробных сведений о работниках этого отделения. Иначе говоря, внутренний оператор SELECT возвращает таблицу, состоящую из единственного значения "BOOV. Оно представляет собой номер того отделения компании, которое находится по адресу "163 Main St1. Б результате внешний оператор SELECT приобретает следующий вид:
SELECT staffNo, fName, IName, position
FROM Staff
WHERE branchNo = "B0031;
Результаты выполнения этого запроса представлены в табл. 29.
Таблица 29
Результат выполнения запроса
| staffNo | fName | IName | position |
| SG37 | Ann | Beech | Assistant |
| SG14 | David | Ford | Supervisor |
| SG5 | Susan | Brand | Manager |
Подзапрос представляет собой инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Подзапрос, можно указывать непосредственно после операторов сравнения (т.е. операторов =, <, >, <=, >=, <>) в конструкции WHERE или HAVING. Текст подзапроса должен быть заключен в круглые скобки.
Пример 20. Использование подзапросов с агрегирующими функциями. Составьте список всех сотрудников, имеющих зарплату выше средней, указав, насколько их зарплата превышает среднюю зарплату по предприятию.
SELECT staffNo, fName, IName, position, salary - (SELECT AVG (salary) FROM Staff) AS salDiff
FROM Staff
WHERE salary > (SELECT AVG (salary) FROM S t a f f) ;
Необходимо отметить, что нельзя непосредственно включить в запрос выражение "WHERE salary > AVG (salary)", поскольку применять агрегирующие функции в конструкции WHERE запрещено. Для достижения желаемого результата следует создать подзапрос, вычисляющий среднее значение годовой заработной платы, а затем использовать его во внешнем операторе SELECT, предназначенном для выборки сведений о тех работниках компании, чья зарплата превышает это среднее значение. Иначе говоря, подзапрос возвращает значение средней зарплаты по компании в год, равное 17 000 фунтов стерлингов.
Результат выполнения этого скалярного подзапроса используется во внешнем операторе SELECT как для вычисления отклонения зарплаты от среднего уровня, так и для отбора сведений о работниках. Поэтому внешний оператор SELECT приобретает следующий вид:
SELECT staffNo, fName, IName, position, salary - 17000 As salDiff
FROM Staff
WHERE salary > 17000;
Результаты выполнения запроса представлены в табл. 30.
Таблица 30.
Результат выполнения запроса
| staffNo | fName | IName | position | salDiff |
| SL21 | John | White | Manager | 13000.00 |
| SG14 | David | Ford | Supervisor | 1000.00 |
| SG5 | Susan | Brand | Manager | 7000.00 |
К подзапросам применяются следующие правила и ограничения.
1. В подзапросах не должна использоваться конструкция ORDER BY, хотя она может присутствовать во внешнем операторе SELECT.
2. Список выборки SELECT подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда в подзапросе используется ключевое слово EXISTS.
3. По умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в конструкции FROM подзапроса. Однако разрешается ссылаться и на столбцы таблицы, указанной в конструкции FROM внешнего запроса, для чего используются уточненные имена столбцов (как описано ниже).
4. Если подзапрос является одним из двух операндов, участвующих в операции сравнения, то подзапрос должен указываться в правой части этой операции. Например, приведенный ниже вариант записи запроса из предыдущего примера является некорректным, поскольку подзапрос размещен в левой части операции сравнения со значением столбца salary.
SELECT
FROM Staff
WHERE (SELECT AVG(salary) FROM Staff) < salary;
Пример 21 . Вложенные подзапросы и использование предиката IN. Составьте перечень сдаваемых в аренду объектов, за которые отвечают работникиотделения компании, расположенного по адресу"163 Main st1.
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
Глава 5 . Язык SQL: манипулирование данными 189
WHERE staffNo IN (SELECT staffNo
FROM Staff
WHERE brancliNo = (SELECT branchNo
FROM Branch
WHERE street = "163 Main S t ")) ;
Первый, самый внутренний, запрос предназначен для определения номера отделения компании, расположенного по адресу 463 Main St". Второй, промежуточный, запрос осуществляет выборку сведений о персонале, работающем в этом отделении. В данном случае выбирается больше одной строки данных и поэтому во внешнем запросе нельзя использовать оператор сравнения =. Вместо него необходимо использовать ключевое слово IN. Внешний запрос осуществляет выборку сведений о сдаваемых в аренду объектах, за которые отвечают те работники компании, данные о которых были получены в результате выполнения промежуточного запроса. Результаты выполнения запроса представлены в табл. 31.
Таблица 31
Результат выполнения запроса
| propertyNo | street | city | postcode | type | rooms | rent |
| PG16 | 5 Novar Dr | Glasgow | G129AX | Flat | ||
| PG36 | 2 Manor Rd | Glasgow | G324QX | Flat | ||
| PG21 | 18 Dale Rd | Glasgow | G12 | House |
Ключевые слова ANY и ALL. Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным только в том случае, если оно выполняется для всех значений в результирующем столбце подзапроса. Если тексту подзапроса предшествует ключевое слово ANY, то условие сравнения будет считаться выполненным, если оно удовлетворяется хотя бы для какого-либо (одного или нескольких) значения в результирующем столбце подзапроса. Если в результате выполнения подзапроса будет получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. Согласно стандарту ISO дополнительно можно использовать ключевое слово SOME, являющееся синонимом ключевого слова ANY.
Пример 22. Использование ключевых слов ANY и SOME. Найдите всех работников, чья зарплата превышает зарплату хотя бы одного работника отделения компании под номером "вооз ".
SELECT staffNo, fName, IName, position, salary
FROM Staff
WHERE salary > SOME(SELECT salary
FROM Staff
WHERE branchNo = "B003");
Хотя этот запрос может быть записан с использованием подзапроса, определяющего минимальную зарплату персонала отделения под номером "ВООЗ", после чего внешний подзапрос сможет выбрать сведения обо всем персонале компании, чья зарплата превосходит это значение (см. пример 20), возможен и другой подход, заключающийся в использовании ключевых слов SOME/ANY. В этом случае внутренний подзапрос создает множество значений {12000, 18000, 24000}, а внешний запрос выбирает сведения о тех работниках, чья зарплата больше любого из значений в этом
множестве (фактически больше минимального значения – 12000). Подобный альтернативный метод можно считать более естественным, чем определение в подзапросе минимальной зарплаты. Но и в том и в ином случае вырабатываются одинаковые результаты выполнения запроса, которые представлены в табл. 32.
Таблица 32
Результат выполнения запроса
| staffNo | fName | IName | position | salary |
| SL21 | John | White | Manager | 30000.00 |
| SG14 | David | Ford | Supervisor | 18000.00 |
| SG5 | Susan | Brand | Manager | 24000.00 |
Пример 23. Использование ключевого слова ALL . Найдите всех работников, чья заработная плата больше заработной платы любого работника отделения компании под номером "вооз ".
SELECT staffNo, fName, INarae, position, salary
FROM Staff
WHERE salary > ALL {SELECT salary
FROM Staff
WHERE branchNo = "BOG3");
В целом этот запрос подобен предыдущему. И в данном случае можно было бы использовать подзапрос, определяющий максимальное значение зарплаты персонала отделения под номером "ВООЗ", после чего с помощью внешнего запроса выбрать сведения обо всех работниках компании, зарплата которых превышает это значение. Однако в данном примере выбран подход с использованием ключевого слова ALL. Результаты выполнения запроса представлены в табл. 33.
Таблица 33
Результат выполнения запроса
| staffNo | IName | fName | position | salary |
| SL21 | White | John | Manager | 30000,00 |
Многотабличные запросы. Все рассмотренные выше примеры имеют одно и то же важное ограничение: помещаемые в результирующую таблицу столбцы всегда выбираются из единственной таблицы. Однако во многих случаях этого оказывается недостаточно. Для того чтобы объединить в результирующей таблице столбцы из нескольких исходных таблиц, необходимо выполнить операцию соединения. В языке SQL операция соединения используется для объединения информации из двух таблиц посредством образования пар связанных строк, выбранных из каждой таблицы. Помещаемые в объединенную таблицу пары строк составляются по равенству входящих в них значений указанных столбцов.
Если необходимо получить информацию из нескольких таблиц, то можно либо применить подзапрос, либо выполнить соединение таблиц. Если результирующая таблица запроса должна содержать столбцы из разных исходных таблиц, то целесообразно использовать механизм соединения таблиц. Для выполнения соединения достаточно в конструкции FROM указать имена двух и более таблиц, разделив их запятыми, после чего включить в запрос конструкцию WHERE с определением столбцов, используемых для соединения указанных таблиц. Помимо этого, вместо имен таблиц можно использовать псевдонимы, назначенные им в конструкции FROM. В этом случае имена таблиц и назначаемые им псевдонимы должны разделяться пробелами. Псевдонимы могут использоваться с целью уточнения имен столбцов во всех тех случаях, когда возможна неоднозначность в отношении того, к какой таблице относится тот или иной столбец. Кроме того, псевдонимы могут использоваться для сокращенного обозначения имен таблиц. Если для таблицы определен псевдоним, он может применяться в любом месте, где требуется указание имени этой таблицы.
Пример 24. Простое соединение. Составьте список имен всех клиентов, которые уже осмотрели хотя бы один сдаваемый в аренду объект и сообщили свое мнение по этому поводу.
SELECT c.clientNo, fName, IName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
В этом отчете требуется представить сведения как из таблицы Client, так и из таблицы Viewing, поэтому при построении запроса мы воспользуемся механизмом соединения таблиц. В конструкции SELECT перечисляются все столбцы, которые должны быть помещены в результирующую таблицу запроса. Обратите внимание, что для столбца с номером клиента (clientNo) необходимо уточнение, поскольку такой столбец может присутствовать и в другой таблице, участвующей в соединении. Поэтому необходимо явно указать, значения какой таблицы нас интересуют. (В данном примере с тем же успехом можно было выбрать значения столбца clientNo из таблицы Viewing). Уточнение имени осуществляется путем указания в качестве префикса перед именем столбца имени соответствующей таблицы {или ее псевдонима). В нашем примере используется значение " с", заданное как псевдоним таблицы Client. Для формирования результирующих строк используются те строки исходных таблиц, которые имеют идентичное значение в столбце clientNo. Это условие определяется посредством задания условия поиска с.clientNo=v.clientNo. Подобные столбцы исходных таблиц называют сочетаемыми столбцами. Описанная операция эквивалентна операции соединения по равенству реляционной алгебры. Результаты выполнения запроса представлены в табл. 34.
Таблица 34
Результат выполнения запроса
| clientNo | fName | IName | propertyNo | comment |
| CR56 | Aline | Stewart | PG36 | |
| CR56 | Aline | Stewart | PA14 | too small |
| CR56 | Aline | Stewart | PG4 | |
| CR62 | Mary | Tregear | PA14 | no dining room |
| CR76 | John | Kay | PG4 | too remote |
Чаще всего многотабличные запросы выполняются для двух таблиц, соединенных связью типа "один ко многим" (1:*), или родительско-дочерней связью. В приведенном выше примере, включающем обращение к таблицам Client и Viewing, последние соединены именно такой связью. Каждая строка таблицы Viewing (дочерней) связана лишь с одной строкой таблицы Client (родительской), тогда как одна и та же строка таблицы Client (родительской) может быть связана
со многими строками таблицы Viewing (дочерней). Пары строк, которые генерируются при выполнении запроса, представляют собой результат всех допустимых комбинаций строк дочерней и родительской таблиц. В разделе 3.2.5 было подробно описано, как в реляционной базе данных первичный и внешний ключи таблиц создают "родительско-дочернюю" связь. Таблица, содержащая внешний ключ, обычно является дочерней, тогда как таблица, содержащая первичный ключ, всегда будет родительской. Для использования родительско-дочерней связи в запросе SQL необходимо указать условие поиска, в котором будут сравниваться внешний и первичный ключи. В примере 24 первичный ключ таблицы Client (с. clientNo) сравнивается с внешним ключом таблицы Viewing (v. clientNo).
Стандарт SQL дополнительно предоставляет следующие способы определения данного соединения:
FROM Client с JOIN Viewing v ON с.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
В каждом случае конструкция FROM замещает исходные конструкции FROM и WHERE. Однако в первом варианте создается таблица с двумя идентичными столбцами clientNo, тогда как в остальных двух случаях результирующая таблица будет содержать только один столбец clientNo.
Пример 25. Сортировка результатов соединения таблиц. Для каждого отделения компании перечислите табельные номера и имена работников, отвечающих за какие-либо сдаваемые в аренду объекты, а также укажите объекты, за
которые они отвечают.
SELECT s.branchNo, s.staffNo, fName, IName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
Для того чтобы результаты стали более удобными для чтения, полученный вывод отсортирован с использованием номера отделения в качестве старшего ключа сортировки, а табельного номера и номера собственности – в качестве младших ключей. Результаты выполнения запроса представлены в табл. 35.
Таблица 35
Результат выполнения запроса
| branchNo | StaffNo | fName | IName | propertyNo |
| ВООЗ | SG14 | David | Ford | PG16 |
| ВООЗ | SG37 | Ann | Beech | PG21 |
| ВООЗ | SG37 | Ann | Beech | PG36 |
| BOO5 | SL41 | Mary | Lee | PL94 |
| ВОО7 | SA9 | Julie | Howe | PA14 |
Пример 26. Соединение трех таблиц. Для каждого отделения компании перечислите табельные номера и имена работников, отвечающих за какие-либо сдаваемые в аренду объекты, с указанием города, в котором расположено данное отделение компании, и номеров объектов, за которые отвечает каждый работник.
SELECT b.branchNo, b.city, s.staffNo, fName, IName, propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
В результирующую таблицу необходимо поместить столбцы из трех исходных таблиц – Branch, Staff и PropertyForRent, поэтому в запросе следует выполнить соединение этих таблиц. Таблицы Branch и Staff могут быть соединены с помощью условия b.branchNo=*s .branchNo, в результате чего отделения компании будут связаны с работающим в них персоналом. Таблицы Staff и PropertyForRent могут быть соединены с помощью условия s.staffNo=p.staffNo. В результате каждый работник будет связан с теми сдаваемыми в аренду объектами, за которые он отвечает. Результаты выполнения запроса представлены в табл. 36.
Таблица 36
Результаты выполнения запроса
| branchNo | city | staffMo | fName | IName | propertyNo |
| В003 | Glasgow | SG14 | David | Ford | PG16 |
| В003 | Glasgow | SG37 | Ann | Beech | PG21 |
| В003 | Glasgow | SG37 | Ann | Beech | PG36 |
| В005 | London | SL41 | Julie | Lee | PL94 |
| В007 | Aberdeen | SA9 | Mary | Howe | PA14 |
Заметим, что стандарт SQL позволяет использовать альтернативный вариант формулировки конструкций FROM и WHERE:
FROM (Branch b JOIN Staff s USING branchNo) AS bs
JOIN PropertyForRent p USING staffNo
Пример 27. Группирование по нескольким столбцам. Определите количество сдаваемых в аренду объектов, за которые отвечает каждый из работников компании.
SELECT s.branchNo, S.staffNo, COUNT (*) AS count
FROM Staff s, PropertyForRent p
WHERE S.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
Чтобы составить требуемый отчет, прежде всего необходимо выяснить, кто из работников компании отвечает за сдаваемые в аренду объекты. Эту задачу можно решить посредством соединения таблиц Staff и PropertyForRent по столбцу staffNo в конструкциях FROM/WHERE. Затем необходимо сформировать группы, состоящие из номера отделения и табельных номеров его работников, для чего следует применить конструкцию GROUP BY. Наконец, результирующая таблица должна быть отсортирована с помощью задания конструкции ORDER BY. Результаты выполнения запроса представлены а табл. 37.
Таблица 37
Результат выполнения запроса
| branchNo | staffNo | count |
| В00З | SG14 | |
| В00З | SG37 | |
| В005 | SL41 | |
| В007 | SA9 |
Выполнение соединений. Соединение является подмножеством более общей комбинации данных двух таблиц, называемой декартовым . Декартово произведение двух таблиц представляет собой другую таблицу, состоящую из всех возможных пар строк, входящих в состав обеих таблиц. Набор столбцов результирующей таблицы представляет собой все столбцы первой таблицы, за которыми следуют все столбцы второй таблицы. Если ввести запрос к двум таблицам без задания конструкции WHERE, результат выполнения запроса в среде SQL будет представлять собой декартово произведение этих таблиц. Кроме того, стандарт ISO предусматривает специальный формат оператора SELECT, позволяющий вычислить декартово произведение двух таблиц:
SELECT {* j columnList]
FROM tableNamel CROSS JOIN СаЫеУлте2
Еще раз рассмотрим пример в котором соединение таблиц client и Viewing выполняется с использованием общего столбца clientNo, При работе с таблицами, содержимое которых приведено в табл. 3.6 и 3.8, декартово произведение этих таблиц будет включать 20 строк (4 строки таблицы Client x 5 строк таблицы viewing = 20 строк). Это эквивалентно выдаче используемого в примере 5.24 запроса, но без применения конструкции WHERE. Процедура генерации таблицы, содержащей результаты соединения двух таблиц с помощью оператора SELECT, состоит в следующем.
1. Формируется декартово произведение таблиц, указанных в конструкции FROM.
2. Если в запросе присутствует конструкция WHERE, применение условий поиска к каждой строке таблицы декартова произведения и сохранение в таблице только тех строк, которые удовлетворяют заданным условиям. В терминах реляционной алгебры эта операция называется ограничением декартового произведения.
3. Для каждой оставшейся строки определяется значение каждого элемента, указанного в списке выборки SELECT, в результате чего формируется отдельная строка результирующей таблицы.
4. Если в исходном запросе присутствует конструкция SELECT DISTINCT, из результирующей таблицы удаляются все строки-дубликаты.
5. Если выполняемый запрос содержит конструкцию ORDER BY,
©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-07
Следующие подразделы описывают другие предложения оператора SELECT, которые могут быть использованы в запросах, а также агрегатные функции и наборы операторов. Напомню, к данному моменту мы рассмотрели использование предложения WHERE, а в этой статье мы рассмотрим предложения GROUP BY, ORDER BY и HAVING, и предоставим некоторые примеры использования этих предложений в сочетании с агрегатными функциями, которые поддерживаются в Transact-SQL.
Предложение GROUP BY
Предложение GROUP BY группирует выбранный набор строк для получения набора сводных строк по значениям одного или нескольких столбцов или выражений. Простой случай применения предложения GROUP BY показан в примере ниже:
USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;
В этом примере происходит выборка и группирование должностей сотрудников.
В примере выше предложение GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL) столбца Job.
Использование столбцов в предложении GROUP BY должно отвечать определенным условиям. В частности, каждый столбец в списке выборки запроса также должен присутствовать в предложении GROUP BY. Это требование не распространяется на константы и столбцы, являющиеся частью агрегатной функции. (Агрегатные функции рассматриваются в следующем подразделе.) Это имеет смысл, т.к. только для столбцов в предложении GROUP BY гарантируется одно значение для каждой группы.
Таблицу можно сгруппировать по любой комбинации ее столбцов. В примере ниже демонстрируется группирование строк таблицы Works_on по двум столбцам:
USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Результат выполнения этого запроса:
По результатам выполнения запроса можно видеть, что существует девять групп с разными комбинациями номера проекта и должности. Последовательность имен столбцов в предложении GROUP BY не обязательно должна быть такой же, как и в списке столбцов выборки SELECT.
Агрегатные функции
Агрегатные функции используются для получения суммарных значений. Все агрегатные функции можно разделить на следующие категории:
обычные агрегатные функции;
статистические агрегатные функции;
агрегатные функции, определяемые пользователем;
аналитические агрегатные функции.
Здесь мы рассмотрим первые три типа агрегатных функций.
Обычные агрегатные функции
Язык Transact-SQL поддерживает следующие шесть агрегатных функций: MIN , MAX , SUM , AVG , COUNT , COUNT_BIG .
Все агрегатные функции выполняют вычисления над одним аргументом, который может быть или столбцом, или выражением. (Единственным исключением является вторая форма двух функций: COUNT и COUNT_BIG, а именно COUNT(*) и COUNT_BIG(*) соответственно.) Результатом вычислений любой агрегатной функции является константное значение, отображаемое в отдельном столбце результата.
Агрегатные функции указываются в списке столбцов инструкции SELECT, который также может содержать предложение GROUP BY. Если в инструкции SELECT отсутствует предложение GROUP BY, а список столбцов выборки содержит, по крайней мере, одну агрегатную функцию, тогда он не должен содержать простых столбцов (кроме как столбцов, служащих аргументами агрегатной функции). Поэтому код в примере ниже неправильный:
USE SampleDb; SELECT LastName, MIN(Id) FROM Employee;
Здесь столбец LastName таблицы Employee не должен быть в списке выборки столбцов, поскольку он не является аргументом агрегатной функции. С другой стороны, список выборки столбцов может содержать имена столбцов, которые не являются аргументами агрегатной функции, если эти столбцы служат аргументами предложения GROUP BY.
Аргументу агрегатной функции может предшествовать одно из двух возможных ключевых слов:
ALLУказывает, что вычисления выполняются над всеми значениями столбца. Это значение по умолчанию.
DISTINCTУказывает, что для вычислений применяются только уникальные значения столбца.
Агрегатные функции MIN и MAX
Агрегатные функции MIN и MAX вычисляют наименьшее и наибольшее значение столбца соответственно. Если запрос содержит предложение WHERE, функции MIN и MAX возвращают наименьшее и наибольшее значение строк, отвечающих указанным условиям. В примере ниже показано использование агрегатной функции MIN:
USE SampleDb; -- Вернет 2581 SELECT MIN(Id) AS "Минимальное значение Id" FROM Employee;
Возвращенный в примере выше результат не очень информативный. Например, неизвестна фамилия сотрудника, которому принадлежит этот номер. Но получить эту фамилию обычным способом невозможно, потому что, как упоминалось ранее, явно указать столбец LastName не разрешается. Для того чтобы вместе с наименьшим табельным номером сотрудника также получить и фамилию этого сотрудника, используется подзапрос. В примере ниже показано использование такого подзапроса, где вложенный запрос содержит инструкцию SELECT из предыдущего примера:
Результат выполнения запроса:
Использование агрегатной функции MAX показано в примере ниже:
В качестве аргумента функции MIN и MAX также могут принимать строки и даты. В случае строкового аргумента значения сравниваются, используя фактический порядок сортировки. Для всех аргументов временных данных типа "дата" наименьшим значением столбца будет наиболее ранняя дата, а наибольшим - наиболее поздняя.
С функциями MIN и MAX можно применять ключевое слово DISTINCT. Перед применением агрегатных функций MIN и MAX из столбцов их аргументов исключаются все значения NULL.
Агрегатная функция SUM
Агрегатная функция SUM вычисляет общую сумму значений столбца. Аргумент этой агрегатной функции всегда должен иметь числовой тип данных. Использование агрегатной функции SUM показано в примере ниже:
USE SampleDb; SELECT SUM (Budget) "Суммарный бюджет" FROM Project;
В этом примере происходит вычисление общей суммы бюджетов всех проектов. Результат выполнения запроса:
В этом примере агрегатная функция группирует все значения бюджетов проектов и определяет их общую сумму. По этой причине запрос содержит неявную функцию группирования (как и все аналогичные запросы). Неявную функцию группирования из примера выше можно указать явно, как это показано в примере ниже:
USE SampleDb; SELECT SUM (Budget) "Суммарный бюджет" FROM Project GROUP BY();
Использование параметра DISTINCT устраняет все повторяющиеся значения в столбце перед применением функции SUM. Аналогично удаляются все значения NULL перед применением этой агрегатной функции.
Агрегатная функция AVG
Агрегатная функция AVG возвращает среднее арифметическое значение для всех значений столбца. Аргумент этой агрегатной функции всегда должен иметь числовой тип данных. Перед применением функции AVG все значения NULL удаляются из ее аргумента.
Использование агрегатной функции AVG показано в примере ниже:
USE SampleDb; -- Вернет 133833 SELECT AVG (Budget) "Средний бюджет на проект" FROM Project;
Здесь происходит вычисление среднего арифметического значения бюджета для всех бюджетов.
Агрегатные функции COUNT и COUNT_BIG
Агрегатная функция COUNT имеет две разные формы:
COUNT( col_name) COUNT(*)
Первая форма функции подсчитывает количество значений в столбце col_name. Если в запросе используется ключевое слово DISTINCT, перед применением функции COUNT удаляются все повторяющиеся значения столбца. При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значения NULL.
Использование первой формы агрегатной функции COUNT показано в примере ниже:
USE SampleDb; SELECT ProjectNumber, COUNT(DISTINCT Job) "Работ в проекте" FROM Works_on GROUP BY ProjectNumber;
Здесь происходит подсчет количества разных должностей для каждого проекта. Результат выполнения этого запроса:
Как можно видеть в результате выполнения запроса, представленного в примере, значения NULL функцией COUNT не принимались во внимание. (Сумма всех значений столбца должностей получилась равной 7, а не 11, как должно быть.)
Вторая форма функции COUNT, т.е. функция COUNT(*) подсчитывает количество строк в таблице. А если инструкция SELECT запроса с функцией COUNT(*) содержит предложение WHERE с условием, функция возвращает количество строк, удовлетворяющих указанному условию. В отличие от первого варианта функции COUNT вторая форма не игнорирует значения NULL, поскольку эта функция оперирует строками, а не столбцами. В примере ниже демонстрируется использование функции COUNT(*):
USE SampleDb; SELECT Job AS "Тип работ", COUNT(*) "Нужно работников" FROM Works_on GROUP BY Job;
Здесь происходит подсчет количества должностей во всех проектах. Результат выполнения запроса:
Функция COUNT_BIG аналогична функции COUNT. Единственное различие между ними заключается в типе возвращаемого ими результата: функция COUNT_BIG всегда возвращает значения типа BIGINT, тогда как функция COUNT возвращает значения данных типа INTEGER.
Статистические агрегатные функции
Следующие функции составляют группу статистических агрегатных функций:
VARВычисляет статистическую дисперсию всех значений, представленных в столбце или выражении.
VARPВычисляет статистическую дисперсию совокупности всех значений, представленных в столбце или выражении.
STDEVВычисляет среднеквадратическое отклонение (которое рассчитывается как квадратный корень из соответствующей дисперсии) всех значений столбца или выражения.
STDEVPВычисляет среднеквадратическое отклонение совокупности всех значений столбца или выражения.
Агрегатные функции, определяемые пользователем
Компонент Database Engine также поддерживает реализацию функций, определяемых пользователем. Эта возможность позволяет пользователям дополнить системные агрегатные функции функциями, которые они могут реализовывать и устанавливать самостоятельно. Эти функции представляют специальный класс определяемых пользователем функций и подробно рассматриваются позже.
Предложение HAVING
В предложении HAVING определяется условие, которое применяется к группе строк. Таким образом, это предложение имеет такой же смысл для групп строк, что и предложение WHERE для содержимого соответствующей таблицы. Синтаксис предложения HAVING следующий:
HAVING condition
Здесь параметр condition представляет условие и содержит агрегатные функции или константы.
Использование предложения HAVING совместно с агрегатной функцией COUNT(*) показано в примере ниже:
USE SampleDb; -- Вернет "p3" SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT(*)
В этом примере посредством предложения GROUP BY система группирует все строки по значениям столбца ProjectNumber. После этого подсчитывается количество строк в каждой группе и выбираются группы, содержащие менее четырех строк (три или меньше).
Предложение HAVING можно также использовать без агрегатных функций, как это показано в примере ниже:
USE SampleDb; -- Вернет "Консультант" SELECT Job FROM Works_on GROUP BY Job HAVING Job LIKE "К%";
В этом примере происходит группирование строк таблицы Works_on по должности и устранение тех должностей, которые не начинаются с буквы "К".
Предложение HAVING можно также использовать без предложения GROUP BY, хотя это не является распространенной практикой. В таком случае все строки таблицы возвращаются в одной группе.
Предложение ORDER BY
Предложение ORDER BY определяет порядок сортировки строк результирующего набора, возвращаемого запросом. Это предложение имеет следующий синтаксис:
Порядок сортировки задается в параметре col_name. Параметр col_number является альтернативным указателем порядка сортировки, который определяет столбцы по порядку их вхождения в список выборки инструкции SELECT (1 - первый столбец, 2 - второй столбец и т.д.). Параметр ASC определяет сортировку в восходящем порядке, а параметр DESC - в нисходящем. По умолчанию применяется параметр ASC.
Имена столбцов в предложении ORDER BY не обязательно должны быть указаны в списке столбцов выборки. Но это не относится к запросам типа SELECT DISTINCT, т.к. в таких запросах имена столбцов, указанные в предложении ORDER BY, также должны быть указаны в списке столбцов выборки. Кроме этого, это предложение не может содержать имен столбцов из таблиц, не указанных в предложении FROM.
Как можно видеть по синтаксису предложения ORDER BY, сортировка результирующего набора может выполняться по нескольким столбцам. Такая сортировка показана в примере ниже:
В этом примере происходит выборка номеров отделов и фамилий и имен сотрудников для сотрудников, чей табельный номер меньше чем 20 000, а также с сортировкой по фамилии и имени. Результат выполнения этого запроса:
Столбцы в предложении ORDER BY можно указывать не по их именам, а по порядку в списке выборки. Соответственно, предложение в примере выше можно переписать таким образом:
Такой альтернативный способ указания столбцов по их позиции вместо имен применяется, если критерий упорядочивания содержит агрегатную функцию. (Другим способом является использование наименований столбцов, которые тогда отображаются в предложении ORDER BY.) Однако в предложении ORDER BY рекомендуется указывать столбцы по их именам, а не по номерам, чтобы упростить обновление запроса, если в списке выборки придется добавить или удалить столбцы. Указание столбцов в предложении ORDER BY по их номерам показано в примере ниже:
USE SampleDb; SELECT ProjectNumber, COUNT(*) "Количество сотрудников" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Здесь для каждого проекта выбирается номер проекта и количество участвующих в нем сотрудников, упорядочив результат в убывающем порядке по числу сотрудников.
Язык Transact-SQL при сортировке в возрастающем порядке помещает значения NULL в начале списка, и в конце списка - при убывающем.
Использование предложения ORDER BY для разбиения результатов на страницы
Отображение результатов запроса на текущей странице можно или реализовать в пользовательском приложении, или же дать указание осуществить это серверу базы данных. В первом случае все строки базы данных отправляются приложению, чьей задачей является отобрать требуемые строки и отобразить их. Во втором случае, со стороны сервера выбираются и отображаются только строки, требуемые для текущей страницы. Как можно предположить, создание страниц на стороне сервера обычно обеспечивает лучшую производительность, т.к. клиенту отправляются только строки, необходимые для отображения.
Для поддержки создания страниц на стороне сервера в SQL Server 2012 вводится два новых предложения инструкции SELECT: OFFSET и FETCH. Применение этих двух предложений демонстрируется в примере ниже. Здесь из базы данных AdventureWorks2012 (которую вы можете найти в исходниках) извлекается идентификатор бизнеса, название должности и день рождения всех сотрудников женского пола с сортировкой результата по названию должности в возрастающем порядке. Результирующий набор строк разбивается на 10-строчные страницы и отображается третья страница:
В предложении OFFSET указывается количество строк результата, которые нужно пропустить в отображаемом результате. Это количество вычисляется после сортировки строк предложением ORDER BY. В предложении FETCH NEXT указывается количество удовлетворяющих условию WHERE и отсортированных строк, которое нужно возвратить. Параметром этого предложения может быть константа, выражение или результат другого запроса. Предложение FETCH NEXT аналогично предложению FETCH FIRST .
Основной целью при создании страниц на стороне сервера является возможность реализация общих страничных форм, используя переменные. Эту задачу можно выполнить посредством пакета SQL Server.
Инструкция SELECT и свойство IDENTITY
Свойство IDENTITY позволяет определить значения для конкретного столбца таблицы в виде автоматически возрастающего счетчика. Это свойство могут иметь столбцы численного типа данных, такого как TINYINT, SMALLINT, INT и BIGINT. Для такого столбца таблицы компонент Database Engine автоматически создает последовательные значения, начиная с указанного стартового значения. Таким образом, свойство IDENTITY можно использовать для создания однозначных числовых значений для выбранного столбца.
Таблица может содержать только один столбец со свойством IDENTITY. Владелец таблицы имеет возможность указать начальное значение и шаг приращения, как это показано в примере ниже:
USE SampleDb; CREATE TABLE Product (Id INT IDENTITY(10000, 1) NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY) INSERT INTO Product(Name, Price) VALUES ("Товар1", 10), ("Товар2", 15), ("Товар3", 8), ("Товар4", 15), ("Товар5", 40); -- Вернет 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Товар5"; -- Аналог предыдущей инструкции SELECT $identity FROM Product WHERE Name = "Товар5";
В этом примере сначала создается таблица Product, содержащая столбец Id со свойством IDENTITY. Значения в столбце Id создаются автоматически системой, начиная с 10 000 и увеличиваясь с единичным шагом для каждого последующего значения: 10 000, 10 001, 10 002 и т.д.
Со свойством IDENTITY связаны некоторые системные функции и переменные. Например, в коде примера используется системная переменная $identity . Как можно видеть по результатам выполнения этого кода, эта переменная автоматически ссылается на свойство IDENTITY. Вместо нее можно также использовать системную функцию IDENTITYCOL .
Начальное значение и шаг приращения столбца со свойством IDENTITY можно узнать с помощью функций IDENT_SEED и IDENT_INCR соответственно. Применяются эти функции следующим образом:
USE SampleDb; SELECT IDENT_SEED("Product"), IDENT_INCR("Product")
Как уже упоминалось, значения IDENTITY устанавливаются автоматически системой. Но пользователь может указать явно свои значения для определенных строк, присвоив параметру IDENTITY_INSERT значение ON перед вставкой явного значения:
SET IDENTITY INSERT table name ON
Поскольку с помощью параметра IDENTITY_INSERT для столбца со свойством IDENTITY можно установить любое значение, в том числе и повторяющееся, свойство IDENTITY обычно не обеспечивает принудительную уникальность значений столбца. Поэтому для принудительного обеспечения уникальности значений столбца следует применять ограничения UNIQUE или PRIMARY KEY.
При вставке значений в таблицу после присвоения параметру IDENTITY_INSERT значения on система создает следующее значение столбца IDENTITY, увеличивая наибольшее текущее значение этого столбца.
Оператор CREATE SEQUENCE
Применение свойства IDENTITY имеет несколько значительных недостатков, наиболее существенными из которых являются следующие:
применение свойства ограничивается указанной таблицей;
новое значение столбца нельзя получить иным способом, кроме как применив его;
свойство IDENTITY можно указать только при создании столбца.
По этим причинам в SQL Server 2012 вводятся последовательности, которые обладают той же семантикой, что и свойство IDENTITY, но при этом не имеют ранее перечисленных недостатков. В данном контексте последовательностью называется функциональность базы данных, позволяющая указывать значения счетчика для разных объектов базы данных, таких как столбцы и переменные.
Последовательности создаются с помощью инструкции CREATE SEQUENCE . Инструкция CREATE SEQUENCE определена в стандарте SQL и поддерживается другими реляционными системами баз данных, такими как IBM DB2 и Oracle.
В пример ниже показано создание последовательности в SQL Server:
USE SampleDb; CREATE SEQUENCE dbo.Sequence1 AS INT START WITH 1 INCREMENT BY 5 MINVALUE 1 MAXVALUE 256 CYCLE;
В примере выше значения последовательности Sequence1 создаются автоматически системой, начиная со значения 1 с шагом 5 для каждого последующего значения. Таким образом, в предложении START указывается начальное значение, а в предложении INCREMENT - шаг. (Шаг может быть как положительным, так и отрицательным.)
В следующих двух, необязательных, предложениях MINVALUE и MAXVALUE указываются минимальное и максимальное значение объекта последовательности. (Обратите внимание, что значение MINVALUE должно быть меньшим или равным начальному значению, а значение MAXVALUE не может быть большим, чем верхний предел типа данных, указанных для последовательности.) В предложении CYCLE указывается, что последовательность повторяется с начала по превышению максимального (или минимального для последовательности с отрицательным шагом) значения. По умолчанию это предложение имеет значение NO CYCLE, что означает, что превышение максимального или минимального значения последовательности вызывает исключение.
Основной особенностью последовательностей является их независимость от таблиц, т.е. их можно использовать с любыми объектами базы данных, такими как столбцы таблицы или переменные. (Это свойство положительно влияет на хранение и, соответственно, на производительность. Определенную последовательность хранить не требуется; сохраняется только ее последнее значение.)
Новые значения последовательности создаются с помощью выражения NEXT VALUE FOR , применение которого показано в примере ниже:
USE SampleDb; -- Вернет 1 SELECT NEXT VALUE FOR dbo.sequence1; -- Вернет 6 (следующий шаг) SELECT NEXT VALUE FOR dbo.sequence1;
С помощью выражения NEXT VALUE FOR можно присвоить результат последовательности переменной или ячейке столбца. В примере ниже показано использование этого выражения для присвоения результатов столбцу:
USE SampleDb; CREATE TABLE Product (Id INT NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY) INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Товар1", 10); INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Товар2", 15); -- ...
В примере выше сначала создается таблица Product, состоящая из четырех столбцов. Далее, две инструкции INSERT вставляют в эту таблицу две строки. Первые две ячейки первого столбца будут иметь значения 11 и 16.
В примере ниже показано использование представления каталога sys.sequences для просмотра текущего значения последовательности, не используя его:
Обычно выражение NEXT VALUE FOR применяется в инструкции INSERT, чтобы система вставляла созданные значения. Это выражение также можно использовать, как часть многострочного запроса с помощью предложения OVER.
Для изменения свойства существующей последовательности применяется инструкция ALTER SEQUENCE . Одно из наиболее важных применений этой инструкции связано с параметром RESTART WITH, который переустанавливает указанную последовательность. В примере ниже показано использование инструкции ALTER SEQUENCE для переустановки почти всех свойств последовательности Sequence1:
USE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART WITH 100 INCREMENT BY 50 MINVALUE 50 MAXVALUE 200 NO CYCLE;
Удаляется последовательность с помощью инструкции DROP SEQUENCE .
Операторы работы с наборами
Кроме операторов, рассмотренных ранее, язык Transact-SQL поддерживает еще три оператора работы с наборами: UNION, INTERSECT и EXCEPT.
Оператор UNION
Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении. Соответственно, результатом объединения двух таблиц является новая таблица, содержащая все строки, входящие в одну из исходных таблиц или в обе эти таблицы.
Общая форма оператора UNION выглядит таким образом:
select_1 UNION select_2 { select_3]}...
Параметры select_1, select_2, ... представляют собой инструкции SELECT, которые создают объединение. Если используется параметр ALL, отображаются все строки, включая дубликаты. В операторе UNION параметр ALL имеет то же самое значение, что и в списке выбора SELECT, но с одним отличием: для списка выбора SELECT этот параметр применяется по умолчанию, а для оператора UNION его нужно указывать явно.
В своей исходной форме база данных SampleDb не подходит для демонстрации применения оператора UNION. Поэтому в этом разделе создается новая таблица EmployeeEnh, которая идентична существующей таблице Employee, но имеет дополнительный столбец City. В этом столбце указывается место жительства сотрудников.
Создание таблицы EmployeeEnh предоставляет нам удобный случай продемонстрировать использование предложения INTO в инструкции SELECT. Инструкция SELECT INTO выполняет две операции. Сначала создается новая таблица со столбцами, перечисленными в списке выбора SELECT. Потом строки исходной таблицы вставляются в новую таблицу. Имя новой таблицы указывается в предложении INTO, а имя таблицы-источника указывается в предложении FROM.
В примере ниже показано создание таблицы EmployeeEnh из таблицы Employee:
USE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh ADD City NCHAR(40) NULL;
В этом примере инструкция SELECT INTO создает таблицу EmployeeEnh, вставляет в нее все строки из таблицы-источника Employee, после чего инструкция ALTER TABLE добавляет в новую таблицу столбец City. Но добавленный столбец City не содержит никаких значений. Значения в этот столбец можно вставить посредством среды Management Studio или же с помощью следующего кода:
USE SampleDb; UPDATE EmployeeEnh SET City = "Казань" WHERE Id = 2581; UPDATE EmployeeEnh SET City = "Москва" WHERE Id = 9031; UPDATE EmployeeEnh SET City = "Екатеринбург" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "Санкт-Петербург" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Краснодар" WHERE Id = 25348; UPDATE EmployeeEnh SET City = "Казань" WHERE Id = 28559; UPDATE EmployeeEnh SET City = "Пермь" WHERE Id = 29346;
Теперь мы готовы продемонстрировать использование инструкции UNION. В примере ниже показан запрос для создания соединения таблиц EmployeeEnh и Department, используя эту инструкцию:
USE SampleDb; SELECT City AS "Город" FROM EmployeeEnh UNION SELECT Location FROM Department;
Результат выполнения этого запроса:
Объединять с помощью инструкции UNION можно только совместимые таблицы. Под совместимыми таблицами имеется в виду, что оба списка столбцов выборки должны содержать одинаковое число столбцов, а соответствующие столбцы должны иметь совместимые типы данных. (В отношении совместимости типы данных INT и SMALLINT не являются совместимыми.)
Результат объединения можно упорядочить, только используя предложение ORDER BY в последней инструкции SELECT, как это показано в примере ниже. Предложения GROUP BY и HAVING можно применять с отдельными инструкциями SELECT, но не в самом объединении.
Запрос в этом примере осуществляет выборку сотрудников, которые или работают в отделе d1, или начали работать над проектом до 1 января 2008 г.
Оператор UNION поддерживает параметр ALL. При использовании этого параметра дубликаты не удаляются из результирующего набора. Вместо оператора UNION можно применить оператор OR, если все инструкции SELECT, соединенные одним или несколькими операторами UNION, ссылаются на одну и ту же таблицу. В таком случае набор инструкций SELECT заменяется одной инструкцией SELECT с набором операторов OR.
Операторы INTERSECT и EXCEPT
Два других оператора для работы с наборами, INTERSECT и EXCEPT , определяют пересечение и разность соответственно. Под пересечением в данном контексте имеется набор строк, которые принадлежат к обеим таблицам. А разность двух таблиц определяется как все значения, которые принадлежат к первой таблице и не присутствуют во второй. В примере ниже показано использование оператора INTERSECT:
Язык Transact-SQL не поддерживает использование параметра ALL ни с оператором INTERSECT, ни с оператором EXCEPT. Использование оператора EXCEPT показано в примере ниже:
Следует помнить, что эти три оператора над множествами имеют разный приоритет выполнения: оператор INTERSECT имеет наивысший приоритет, за ним следует оператор EXCEPT, а оператор UNION имеет самый низкий приоритет. Невнимательность к приоритету выполнения при использовании нескольких разных операторов для работы с наборами может повлечь неожиданные результаты.
Выражения CASE
В области прикладного программирования баз данных иногда требуется модифицировать представление данных. Например, людей можно подразделить, закодировав их по их социальной принадлежности, используя значения 1, 2 и 3, обозначив так мужчин, женщин и детей соответственно. Такой прием программирования может уменьшить время, необходимое для реализации программы. Выражение CASE языка Transact-SQL позволяет с легкостью реализовать такой тип кодировки.
В отличие от большинства языков программирования, CASE не является инструкцией, а выражением. Поэтому выражение CASE можно использовать почти везде, где язык Transact-SQL позволяет применять выражения. Выражение CASE имеет две формы:
простое выражение CASE;
поисковое выражение CASE.
Синтаксис простого выражения CASE следующий:
Инструкция с простым выражением CASE сначала ищет в списке всех выражений в предложении WHEN первое выражение, совпадающее с выражением expression_1, после чего выполняет соответствующее предложение THEN . В случае отсутствия в списке WHEN совпадающего выражения, выполняется предложение ELSE .
Синтаксис поискового выражения CASE следующий:
В данном случае выполняется поиск первого отвечающего требованиям условия, после чего выполняется соответствующее предложение THEN. Если ни одно из условий не отвечает требованиям, выполняется предложение ELSE. Применение поискового выражения CASE показано в примере ниже:
USE SampleDb; SELECT ProjectName, CASE WHEN Budget > 0 AND Budget 100000 AND Budget 150000 AND Budget
Результат выполнения этого запроса:
В этом примере взвешиваются бюджеты всех проектов, после чего отображаются вычисленные их весовые коэффициенты вместе с соответствующими наименованиями проектов.
В примере ниже показан другой способ применения выражения CASE, где предложение WHEN содержит вложенные запросы, составляющие часть выражения:
USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (SELECT AVG(p2.Budget) FROM Project p2) THEN "выше среднего" END "Категория бюджета" FROM Project p1;
Результат выполнения этого запроса следующий:
