Справка по SQL

даст результат 1926. В принципе все правильно, т.к. мы получили в результате то, что просили - ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут следует отметить, что агрегатные функции (за исключением функции COUNT, которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched - целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте - не округленное).

А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,

SELECT CAST(AVG(launched) AS NUMERIC(6,2)) FROM ships;

вернет значение 1926.00. Следовательно, CAST нужно применить к аргументу агрегатной функции:

SELECT AVG(CAST(launched AS NUMERIC(6,2))) FROM ships;

Результат - 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2)) FROM ships;

В результате получим то, что нужно - 1926.24. Однако это решение выглядит очень громоздко. Заставим неявное преобразование типа поработать на нас:

SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2)) FROM ships;

Т.е. мы использовали неявное преобразование целочисленного аргумента к точному числовому типу (EXACT NUMERIC), умножив его на вещественную единицу, после чего применили явное приведения типа результата агрегатной функции.

Аналогичные преобразования типа можно выполнить с помощью функции CONVERT:

SELECT CONVERT(NUMERIC(6,2),AVG(launched*1.0)) FROM ships;

Функция CONVERT имеет следующий синтаксис:

CONVERT ([()], [, ])

Основное отличие функции CONVERT от функции CAST состоит в том, что первая позволяет форматировать данные (например, темпоральные данные типа datetime) при преобразовании их к символьному типу и указывать формат при обратном преобразовании. Разные целочисленные значения необязательного аргумента стиль соответствуют определенным форматам. Рассмотрим следующий пример

SELECT CONVERT(char(25),CONVERT(datetime,'20030722'));

Здесь мы преобразуем строковое представление даты к типу datetime, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано, используется значение по умолчанию (0 или 100). В результате получим

Jul 22 2003 12:00AM

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

стиль	формат
1	07/22/03
11	03/07/22
3	22/07/03
121	2003-07-22 00:00:00.000

Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.

Оператор CASE

Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (нет в таблице РС), то вместо цены вывести текст: "Нет в наличии".

Список всех моделей ПК с ценами можно получить с помощью запроса:

SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c

ON product.model=c.model

WHERE product.type='pc';

В результирующем наборе отсутствующая цена будет заменена NULL-значением:

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	NULL
2112	NULL

Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE:

SELECT DISTINCT product.model,

CASE WHEN price IS NULL THEN 'Нет в наличии' ELSE CAST(price AS CHAR(20)) END price

FROM product LEFT JOIN pc c ON product.model=c.model

WHERE product.type='pc'