2.10. Расчеты в Transact-SQL

В SQL включена возможность, выполнять простейшие математические операции над полями и не только над числовыми, но и над строковыми. Например, иногда пользователю очень удобно вывести на экран ФИО в одном поле, а ведь у нас в базе данных эти поля разделены.

SELECT vcFamil+' '+vcName+' '+vcSurname
FROM tbPeoples

В данном случае мы объединяем текстовые поля таблицы в одно целое поле, вставляя между ними пробелы.

Если знак сложения стоит между числовыми полями, то происходит не объединение, а сложение числовых значений:

SELECT vcFamil+' '+vcName+' '+vcSurname, idPeoples+idPosition
FROM tbPeoples

В данном примере мы складываем поля "idPeoples" и "idPosition". Конечно, именно в данном сложении смысла нет, потому что это идентификаторы. Но сама операция вполне полезна. Например, если у вас есть таблица, в которой показаны цены и количество товаров на складе, то можно цену из базы таблицы динамически перемножить с количеством:

SELECT название, количество, цена, количество*цена AS сумма
FROM Таблица

В данном примере используются псевдо имена какой-то таблицы. Для удобства я написал названия полей простыми русскими словами. В этом случае нет необходимости создавать вычисляемые поля, которые мы рассматривали в главе 1.2.8, хотя, в зависимости от задачи, вычисляемое поле может сэкономить ресурсы. Ведь в вычисляемом поле вычисление происходит, только когда данные изменяются, а если расчеты производить в запросе, то они будут выполняться при каждом обращении к таблице с данными.

Если к таблице обращается 1000 пользователей в минуту, и при этом данные изменяются очень редко, то вычисляемое поле будет намного эффективнее. Но если к данным обращается 1 человек раз в час, а изменяют их 100 человек каждые 5 минут, то тут уже намного эффективнее будет рассчитывать в запросе. Как видите, выбираемый метод зависит от ситуации и трудно сказать, когда и что удобнее использовать.

Математические операции можно производить не только между полями. Например, давайте на время представим, что поле "idPosition" – это не ключ, а сумма заработной платы в месяц в тысячах долларов. Как узнать зарплату в рублях? Очень просто:

SELECT vcFamil+' '+vcName+' '+vcSurname, idPosition*1000*27
FROM tbPeoples

В данном примере, поле "idPosition" сначала умножается на 1000 (мы же договорились, что зарплата в тысячах долларов) и потом умножаем на 27 (это примерный курс доллара на момент написания этой книги).

Вычисляемые поля не имеют имени, поэтому в результате в соответствующей колонке может быть написано что-то типа (no column name). В некоторых программах вообще имя поле будет пустым. Чтобы задать имя используйте ключевое слово AS.

А что, если прибавить к числовому полю текст? Если исходить из стандарта, то по идее все преобразования должны происходить автоматически. В SQL Server автомата не будет, придется явно приводить тип:

SELECT vcFamil+' '+vcName+' '+vcSurname, 
 cast((idPosition*1000*27) AS varchar(50))+' руб'
FROM tbPeoples

Результат работы запроса можно увидеть на рисунке 2.5. В этом примере используется функция cast, которую мы еще не рассматривали, но обязательно рассмотрим в будущем. А пока вам достаточно видеть, что все возможно.

С помощью подобных операций можно улучшать вывод. Например, давайте выведем в одной колонке ФИО, а в другой колонке номер телефона. Но перед номером телефона будем выводить слово: телефон. Эта задача решается следующим запросом:

SELECT vcFamil+' '+vcName+' '+vcSurname AS 'ФИО',
   'Телефон: '+vcPhoneNumber AS 'Номер телефона'
FROM tbPeoples pl, tbPhoneNumbers ps
WHERE pl.idPeoples=ps.idPeoples

В данном примере, мы объединяем слово 'Телефон: ' и содержимое поле телефона.

Здесь есть маленький недостаток, приходиться перечислять поля, которые нужно вывести и * уже не работает. Так что если понадобиться вывести все поля, то придется их все писать после команды SELECT.

Помимо простых математических команд над отдельными строками, можно выполнять расчеты над всей таблицей. В стандарт заложены следующие команды:

• COUNT - подсчет количества строк;
• SUM - подсчет суммы;
• AVG - подсчет среднего значения;
• MAX - поиск максимального значения;
• MIN - поиск минимального значения.

Всем этим функциям нужно после имени в скобках указать имя поля, по которому нужно производить расчет. Единственная функция, которой все равно, что указывать в скобках – COUNT. Какая разница, в каком поле считать количество строк, оно всегда будет одним и тем же. Поэтому для этой функции можно указывать любое поле, а большинство программистов просто указывает знак звездочки, т.е. считать по всем полям.

Теперь рассмотрим примеры использования этих функций. Для начала научимся определять количество записей. Самое простое – узнать, сколько записей в таблице. Следующий запрос определяет количество записей в таблице tbPeoples:

SELECT COUNT(*)
FROM tbPeoples

В результате мы получим результат в виде таблицы, но всего из одной строки и одной колонки, которая будет содержать только одно число – количество записей. Для нашей таблицы tbPeoples это число 19.

А что, если нужно определить количество записей, в которых поле "vcName" содержит имя Андрей? Этот запрос не намного сложнее, нужно всего лишь в разделе WHERE указать нужное условие:

SELECT COUNT(*)
FROM tbPeoples
WHERE vcName='Андрей'

В нашей таблице целых 5 записей с таким именем.

Вторая, часто используемая функция – SUM, которая считает сумму значений во всех строках. Давайте посчитаем сумму значений в колонке "idPosition":

SELECT SUM(idPosition)
FROM tbPeoples

Допустим, что нам нужно определить самую большую дату рождения. Как это можно сделать? Конечно же, функцией MAX:

SELECT MAX(dDateBirthDay)
FROM tbPeoples

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

SELECT MIN(dDateBirthDay)
FROM tbPeoples

Для определения среднего значения, таким же методом используем функцию AVG:

SELECT AVG(dDateBirthDay)
FROM tbPeoples

Зачем нужно вставлять математику в запросы, ведь это можно сделать программно? Вы можете получить данные из базы, и потом выполнять математику, но если есть возможность вставить эту математику в запрос, то лучше это сделать. В этом случае вы освободите клиентскую машину от лишней загрузки, к тому же сервер будет выполнять эти расчеты намного быстрее. Когда вы делаете расчеты программно, то выполняется два просмотра всей базы. Во время первого выбираются данные, а во время второго идет расчет. Когда математика вставлена в запрос, то все действия выполняются за один проход - выбираются данные и одновременно происходят математические расчеты и все это происходит на сервере, который исходя из практики, сделает это намного быстрее, чем клиентский компьютер, ведь сервера стараются устанавливать достаточно мощными.