Обработка баз данных на Visual Basic®.NET
вернуться

Прайс Кевин Т.
Шрифт:

Запросы с группировкой подводят итоги по одному или нескольким полям. Например, если вам интересно увидеть число заказов для каждого клиента, то нужно создать приведенный ниже запрос для таблицы tblOrder с группировкой данных по полю CustomerID.

SELECT CustomerID, COUNT(CustomerID) AS TotalOrders

FROM tblOrder

GROUP BY CustomerID

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

CustomerID TotalOrders
1 2
2 1
4 3
5 1
7 1
8 2

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

Для отображения имен клиентов вместо их идентификаторов нужно просто объединить с результатами запроса данные из таблицы tblCustomer.

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

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

CustomerID FirstName LastName TotalOrders
1 John Smith 2
2 Jill Azalia 1
4 Daisy Klein 3
5 Dave Martin 1
7 Thurston Ryan 1
8 Jane Winters 2

В этом случае предложение GROUP BY содержит поле CustomerID вместе с объединенными полями FirstName и LastName из таблицы tblCustomer. При использовании предложения GROUP BY в него необходимо включить все поля, по которым группируются извлекаемые записи. В данном случае идентификатор клиента и его имя участвуют в группировании данных и потому присутствуют в предложении GROUP BY. (К счастью, если вы забудете выполнить эту операцию, среда Visual Studio .NET автоматически предложит вам сделать это.)

Применение предложения HAVING для группирования данных в запросах

Как уже отмечалось выше, критерий запроса служит для ограничения количества извлекаемых записей. В обычных запросах для включения критериев используется предложение WHERE, в запросах с группированием — предложение HAVING. Эти предложения применяются совершенно одинаково, за исключением того, что HAVING относится к агрегированным строкам (т.е. к результату группирования), a WHERE – к отдельным строкам. Это довольно незначительное отличие, потому что в 9 случаях из 10 они дают совершенно одинаковый результат. Например для создания отчета о продажах клиента Jane с группированием данных можно использовать приведенный ниже запрос.

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 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: