Обработка баз данных на Visual Basic®.NET
Шрифт:
Определение псевдонимов с использованием предложения AS
Из предыдущего примера ясно, что существует возможность определения псевдонимов (alias), т.е. переименования полей в запросе. Это может быть вызвано следующими причинами:
• в основной таблице имена полей могут быть громоздкими, а в результирующем наборе должны быть понятными и простыми;
• запрос создает столбец, который заполняется в результате некоторых вычислений или операций подведения итогов, а новому столбцу обязательно нужно задать имя.
Независимо от причины, это легко сделать в запросе SQL с помощью предложения AS. Например, вам нужно выполнить ряд сложных
Этот запрос возвращает приведенный ниже результирующий набор.
| 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.
Результат выполнения такого запроса приведен ниже.
| CustomerID | TotalOrders |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 4 | 3 |
| 5 | 1 |
| 7 | 1 |
| 8 | 2 |
Обратите внимание на использование предложения AS в этом выражении SQL. Оно предназначено для присвоения имени столбцу, содержащему результат итоговой функции, поскольку этот результат вычисляется, а не хранится в базе данных.
Для отображения имен клиентов вместо их идентификаторов нужно просто объединить с результатами запроса данные из таблицы tblCustomer.
После
| 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 с группированием данных можно использовать приведенный ниже запрос.
Этот запрос возвращает одну запись для клиента Jane Winters с указанием двух сделанных ею заказов. Допустим, теперь нужно получить список активных покупателей, т.е. клиентов, сделавших более одного заказа. Поскольку агрегированное количество заказов хранится в вычисленном поле TotalOrders, можно предположить, что для определения таких клиентов допустимо использовать выражение HAVING TotalOrders > 1. К сожалению, это выражение некорректно, так как TotalOrders – это не поле базы данных, а вычисленное поле. Вместо этого следует включить данное вычисление в предложение HAVING показанного ниже запроса.