Обработка баз данных на Visual Basic®.NET

Определение псевдонимов с использованием предложения AS

Из предыдущего примера ясно, что существует возможность определения псевдонимов (alias), т.е. переименования полей в запросе. Это может быть вызвано следующими причинами:

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

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

Независимо от причины, это легко сделать в запросе SQL с помощью предложения AS. Например, вам нужно выполнить ряд сложных вычислений для определения суммарной стоимости отгруженных товаров ExtendedPrice. Для этого напишите следующий код SQL:

SELECT TOP 5 ItemID, Quantity, Price,
tblInventory.RetailPrice * tblOrderTime.Quantity AS ExtendedPrice
FROM tblOrderTime INNER JOIN
tblInventory ON tblOrderTime.ItemID = tblITem.ID

Этот запрос возвращает приведенный ниже результирующий набор.

ItemID	Quantity	RetailPrice	ExtendedPrice
1	1	5.99	5.99
2	2	1.39	2.78
5	3	2.29	6.87
4	2	3.99	7.98
7	1	5.99	5.99

Обратите внимание, что данные в поле ExtendedPrice не хранятся в базе данных, они вычислены "на лету".

Запросы, которые группируют данные и подводят итоги

Часто требуется создавать запросы, подобные следующему: "Сколько заказов поступило вчера?" При этом вас не интересует, кто оформил заказ, вы только хотите знать количество вчерашних заказов. Это можно сделать, используя запросы, группирующие итоговые функции.

Запросы с группировкой подводят итоги по одному или нескольким полям. Например, если вам интересно увидеть число заказов для каждого клиента, то нужно создать приведенный ниже запрос для таблицы 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)

После выполнения этого запроса будут получены три строки, каждая из которых содержит номер, имя, фамилию и количество заказов для каждого клиента, который сделал более одного заказа.