Главная | Обратная связь | Поможем написать вашу работу!

Ограничения на выполнение группирования (конструкция HAVING)

Конструкция HAVING предназначена для использования совместно с конструкцией GROUP BY для задания ограничений, указываемых с целью отбора тех групп, которые будут помещены в результирующую таблицу запроса. Хотя конструкции HAVING и WHERE имеют сходный синтаксис, их назначение различно.

Конструкция WHERE предназначена для отбора отдельных строк, предназначенных для заполнения результирующей таблицы запроса, а конструкция HAVING используется для отбора групп, помещаемых в результирующую таблицу запроса. Стандарт ISO требует, чтобы имена столбцов, применяемые в конструкции HAVING, обязательно присутствовали в списке элементов GROUP BY или применялись в агрегирующих функциях. На практике условия поиска в конструкции HAVING всегда включают, по меньшей мере, одну агрегирующую функцию; в противном случае эти условия поиска должны быть помещены в конструкцию WHERE и применены для отбора отдельных строк. (Помните, что агрегирующие функции не могут использоваться в конструкции WHERE.)

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

Пример 5.18. Использование конструкции HAVING

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

SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum

FROM Staff

GROUP BY branchNo

HAVING COUNT(staffNo) > 1

ORDER BY branchNo;

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

Таблица 5.21. Результат выполнения запроса из примера 5.18

BranchNo count sum

ВООЗ 3 54000.00

В005 2 39000.00

Подзапросы

В этом разделе мы обсудим использование законченных операторов SELECT, внедренных в тело другого оператора SELECT. Внешний (второй) оператор SELECT использует результат выполнения внутреннего (первого) оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут находиться в конструкциях WHERE и HAVING внешнего оператора SELECT — в этом случае они получают название подзапросов, или вложенных запросов. Кроме того, внутренние операторы SELECT могут использоваться в операторах INSERT, UPDATE и DELETE (см. раздел 5.3.10). Существуют три типа подзапросов.

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

• Строковый подзапрос возвращает значения нескольких столбцов таблицы, но в виде единственной строки. Строковый подзапрос может использоваться везде, где применяется конструктор строковых значений, — обычно ото предикаты. Вариант строкового подзапроса приведен в примере 5.15-

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

как операнд предиката IN.

Пример 5.19. Использование подзапроса с проверкой на равенство

Составьте список персонала, работающею в отделении компании, расположенном по адресу 463 Main St1.

SELECT staffNo, fName, IName, position

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;

Результаты выполнения этого запроса представлены в табл. 5.22.

Таблица 5.22. Результат выполнения запроса из примера 5.19

Подзапрос представляет собой инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Подзапрос, можно указывать непосредственно после операторов сравнения (т.е. операторов =, <, >, <=, >=, <>) в конструкции WHERE или HAVING. Текст подзапроса должен быть заключен в круглые скобки.

Пример 5.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;

Результаты выполнения запроса представлены в табл. 5.23

Таблица 5.23. Результат выполнения запроса из примера 5.20

К подзапросам применяются следующие правила и ограничения.

1. В подзапросах не должна использоваться конструкция ORDER BY, хотя она может присутствовать во внешнем операторе SELECT.

2. Список выборки SELECT подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда в подзапросе используется ключевое слово EXISTS (см. раздел 5.3.8).

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

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

SELECT staffNo, fName, IName, position, salary

FROM Staff

WHERE (SELECT AVG(salary) FROM Staff) < salary;

Пример 5.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. Внешний запрос осуществляет выборку сведений о сдаваемых в аренду объектах, за которые отвечают те работники компании, данные о которых были получены в результате выполнения промежуточного запроса. Результаты выполнения запроса представлены в табл. 5.24.

Таблица 5.24. Результат выполнения запроса из примера 5.21

⇐ Предыдущая 3 4 5 6 789 10 11 12 Следующая ⇒