Использование агрегатных функций в запросах
Пример 21. Получить общее количество поставщиков (ключевое слово COUNT): SELECT COUNT(*) AS NFROM P;В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P:
Пример 22. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG): SELECTSUM(PD.VOLUME) AS SM,MAX(PD.VOLUME) AS MX,MIN(PD.VOLUME) AS MN,AVG(PD.VOLUME) AS AVFROM PD;В результате получим следующую таблицу с одной строкой:
Использование агрегатных функций с группировками Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …): SELECTPD.DNUM,SUM(PD.VOLUME) AS SMGROUP BY PD.DNUM;Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результатирующую таблицу будет включена одна строка:
Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку: SELECTPD.PNUM,PD.DNUM,SUM(PD.VOLUME) AS SMGROUP BY PD.DNUM;Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке. Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.
Пример 24. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …): Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING: SELECTPD.DNUM,SUM(PD.VOLUME) AS SMGROUP BY PD.DNUMHAVING SUM(PD.VOLUME) > 400;В результате получим следующую таблицу:
Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY. Использование подзапросов Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос. Пример 25. Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом): SELECT *FROM PWHERE P.STATYS <(SELECT MAX(P.STATUS) FROM P);Замечание. Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки. Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий: 1. Выполнить один раз вложенный подзапрос и получить максимальное значение статуса. 2. Просканировать таблицу поставщиков P, каждый раз сравнивая значение статуса поставщика с результатом подзапроса, и отобрать только те строки, в которых статус меньше максимального.
Пример 26. Использование предиката IN. Получить список поставщиков, поставляющих деталь номер 2: SELECT *FROM PWHERE P.PNUM IN(SELECT DISTINCT PD.PNUM FROM PDWHERE PD.DNUM = 2);Замечание. В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк. Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий: 1. Выполнить один раз вложенный подзапрос и получить список номеров поставщиков, поставляющих деталь номер 2. 2. Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса. Пример 27. Использование предиката EXIST. Получить список поставщиков, поставляющих деталь номер 2: SELECT *FROM PWHERE EXIST(SELECT *FROM PDWHEREPD.PNUM = P.PNUM ANDPD.DNUM = 2);Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий: 1. Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P. 2. В результат запроса включить только те строки из таблицы поставщиков, для которых вложенный подзапрос вернул непустое множество строк. Замечание. В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXIST, но могут быть использованы и в других подзапросах. Замечание. Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет, как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса, а как этот результат будет получен - за это отвечает сама СУБД.
Пример 28. Использование предиката NOT EXIST. Получить список поставщиков, не поставляющих деталь номер 2: SELECT *FROM PWHERE NOT EXIST(SELECT *FROM PDWHEREPD.PNUM = P.PNUM ANDPD.DNUM = 2);Замечание. Также как и в предыдущем примере, здесь используется коррелируемый подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки. Пример 29. Получить имена поставщиков, поставляющих все детали: SELECT DISTINCT PNAMEFROM PWHERE NOT EXIST(SELECT *FROM DWHERE NOT EXIST(SELECT *FROM PDWHEREPD.DNUM = D.DNUM ANDPD.PNUM = P.PNUM));Замечание. Данный запрос содержит два вложенных подзапроса и реализует реляционную операцию деления отношений. Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXIST говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXIST говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.
Воспользуйтесь поиском по сайту: ![]() ©2015 - 2025 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...
|