COUNT(dbo.tblOrder.CustomerID) AS TotalOrders
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.ID
GROUP BY FirstName, LastName, CustomerID
HAVING FirstName = 'Jane'
Этот запрос возвращает одну запись для клиента Jane Winters с указанием двух сделанных ею заказов. Допустим, теперь нужно получить список активных покупателей, т.е. клиентов, сделавших более одного заказа. Поскольку агрегированное количество заказов хранится в вычисленном поле TotalOrders, можно предположить, что для определения таких клиентов допустимо использовать выражение HAVING TotalOrders > 1. К сожалению, это выражение некорректно, так как TotalOrders – это не поле базы данных, а вычисленное поле. Вместо этого следует включить данное вычисление в предложение HAVING показанного ниже запроса.
SELECT tblOrder.CustomerID, FirstName, LastName,
COUNT(dbo.tblOrder.CustomerID) AS TotalOrders
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.ID
GROUP BY FirstName, LastName, CustomerID
HAVING (COUNT(tblOrder.CustomerID) > 1)
После выполнения этого запроса будут получены три строки, каждая из которых содержит номер, имя, фамилию и количество заказов для каждого клиента, который сделал более одного заказа.
CustomerID FirstName LastName TotalOrders 1 John Smith 2 4 Daisy Klein 3 8 Jane Winters 2
Функция SUM
Ваши возможности в подведении итогов не ограничены простым подсчетом записей. Используя функцию SUM, можно генерировать итоговые результаты для всех возвращаемых записей по любым числовым полям. Например, для создания запроса, который генерирует итоги по количеству заказанных товаров каждым клиентом, необходимо написать следующую команду SQL:
SELECT OrderID, SUM(Quantity) AS TotalItems
FROM tblOrderItem
GROUP BY OrderID
Этот запрос возвращает приведенный ниже результирующий набор.
OrderID TotalItems 1 6 2 2 3 1 4 23 5 4 6 13 7 12 8 3 9 4 10 4
Как и в предыдущих примерах группирования, если вы захотите извлечь дополнительную связанную информацию (например, имя и фамилию клиента), следует использовать объединение с другой таблицей. Помните, что для агрегирования данных потребуется сгруппировать данные по крайней мере по одному полю.
Перечень итоговых функций
В табл. 2.2 перечислены все итоговые функции, доступные в SQL.
Таблица 2.2. Итоговые функции SQL
Функция Результат AVG Среднее значение от всех значений в столбце COUNT Общее количество отобранных записей MAX Максимальное (наибольшее) значение поля MIN Минимальное (наименьшее) значение поля STDEV Среднеквадратическое отклонение SUM Общая сумма всех значений в поле VAR Дисперсия
Синтаксис этих функций, по сути, соответствует синтаксису функции COUNT, которая рассматривалась в предыдущем разделе. Например, для ежедневного вычисления среднего количества товаров в каждом заказе воспользуйтесь приведенным ниже запросом SQL.
SELECT AVG(tblOrderItem.Quantity) AS AverageLineItemQuantity
FROM tblOrder INNER JOIN
tblOrderItem ON tblOrder.ID = tblOrderItem.OrderID
Этот запрос возвращает значение 2, т.е. количество товаров в заказах всех клиентов.
Вычисления и итоговые функции можно комбинировать разными способами. Например, чтобы получить список со стоимостью всех товаров в каждом заказе, нужно определить стоимость товара (эти сведения хранятся в таблице tblInventory) в каждом заказе и умножить ее на количество этих товаров в заказе (эти сведения хранятся в таблице tblOrderItem), а затем сложить полученные произведения в каждом заказе.
SELECT tblOrderItem.OrderID, SUM(Quantity * Price)
AS OrderTotal
FROM tblInventory INNER JOIN
tblOrderItem ON tblItem.ID = tblOrderItem.OrderID GROUP BY OrderID
Этот запрос возвращает приведенный ниже результирующий набор.
OrderID OrderTotal 1 15.64 2 7.98 3 5.99 4 99.17 5 13.96 6 49.07 7 55.88 8 13.97 9 9.16 10 14.76
Запросы на объединение
Запрос на объединение (union query) выполняет объединение содержимого двух таблиц, имеющих одинаковые структуры полей. Это оказывается полезным, когда нужно отобразить в одном результирующем наборе потенциально не связанные записи из нескольких источников.
Далее в главе приводятся примеры сохранения старых заказов в архивной таблице с именем tblOrderArchive. И если вы воспользуетесь предложенной системой архивирования, то записи физически будут размещены в двух отдельных таблицах. Это может повысить эффективность работы: запрос выполняется быстрее на маленькой таблице, чем на большой. Но, возможно, в некоторых случаях понадобится просмотреть все текущие и заархивированные записи в одном общем результирующем наборе. С такой задачей прекрасно справится запрос на объединение.
Предположим, что как раз возникла необходимость в просмотре в одном результирующем наборе старых записей из таблицы tblOrderArchive и новых записей из tblOrder. Такой запрос приведен ниже.
SELECT *
FROM tblOrder
UNION
SELECT *
FROM tblOrderArchive
После выполнения этого запроса старые и новые заказы объединятся в одном результирующем наборе, причем результат будет выглядеть подобно исходной таблице до архивирования.
По умолчанию запрос на объединение не возвращает записи-дубликаты (хотя было бы неплохо, чтобы ваша система архивирования записей не удаляла их после копирования в таблицу архива). Отображение записей-дубликатов может оказаться весьма полезным, если система архивирования старых записей не удаляет записи после копирования в архивную таблицу и вам нужно просмотреть и сравнить некоторые старые и новые записи.
Однако, добавив ключевое слово ALL, можно заставить запрос на объединение отображать дублирующие записи, как показано ниже.
SELECT *
FROM tblOrder
UNION ALL
SELECT *
FROM tblOrderArchive
Подзапросы
Подзапрос (subquery) — это запрос, результат которого служит критерием для другого запроса. Подзапросы занимают место обычного выражения WHERE. Поскольку результат, сгенерированный подзапросом, используется вместо выражения, подзапрос может возвращать только одиночное значение (в противоположность обычному запросу, который возвращает несколько значений, представленных в виде строк и столбцов).
Единственное синтаксическое различие между подзапросом и выражением любого другого типа, размещенным в предложении WHERE, состоит в том, что подзапрос должен быть заключен в круглые скобки. Например, нужно создать запрос, который отображает заказы с самыми дорогими товарами. Дорогим считается такой товар, стоимость которого превышает среднюю стоимость товаров в таблице tblItem. Поскольку среднюю стоимость товара легко определить (выполнив итоговую функцию AVG по полю UnitPrice в таблице tblItem), это значение можно использовать как подзапрос в более крупном запросе. Такой запрос SQL приведен ниже.
