SELECT ID, Item, Price, Price * 0.075 AS SalesTax
FROM tblItem
После выполнения этого запроса будут получен показанный ниже результат.
ID Name Price SalesTax 1 Rubber Chicken 5.99 0.44925 2 Hand Buzzer 1.39 0.10425 3 Stink Bomb 1.29 0.09675 4 Disappearing Penny Magic Trick 3.99 0.29925 5 Invisible Ink 2.29 0.17175 6 Loaded Dice 3.49 0.26175 7 Whoopee Cushion 5.99 0.44925
Поскольку в этих вычислениях фигурируют деньги, конечный результат нужно округлить до двух десятичных знаков. К счастью, в SQL Server для этого предусмотрена специальная функция ROUND, которая позволяет очень легко выполнить это. Обычно ее используют с указанием двух параметров: собственно десятичного числа и точности, выраженной в виде количества знаков после запятой. Вот как выглядит запрос с функцией ROUND:
SELECT Name, Retail Price, ROUND (Retail Price + Retail Price * 0.075, 2)
AS PriceWithTax
FROM tblInventory
Результат выполнения этого запроса приведен ниже.
Name Retail Price PriceWithTax Rubber Chicken 5.99 6.44 Hand Buzzer 1.39 1.49 Stink Bomb 1.29 1.39 Disappearing Penny Magic Trick 3.99 4.29 Invisible Ink 2.29 2.46 Loaded Dice 3.49 3.75 Whoopee Cushion 5.99 6.44
Определение псевдонимов с использованием предложения 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'
