А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения 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'
Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст "Нет в наличии", в противном случае (ELSE) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим
|
model |
price |
|---|---|
|
1121 |
850 |
|
1232 |
350 |
|
1232 |
400 |
|
1232 |
600 |
|
1233 |
600 |
|
1233 |
950 |
|
1233 |
980 |
|
1260 |
350 |
|
2111 |
Нет в наличии |
|
2112 |
Нет в наличии |
Оператор CASE может быть использован в одной из двух синтаксических форм записи:
1-я форма
CASE
WHEN
THEN
…
WHEN
THEN
[ELSE ]
END
2-я форма
CASE
WHEN
THEN
…
WHEN
THEN
[ELSE ]
END
Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.