W Cat - Справка по SQL

В результате получим то, что нужно - 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). В результате получим

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

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

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

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

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

ON product.model=c.model

WHERE product.type='pc';

В результирующем наборе отсутствующая цена будет заменена 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 применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим

Оператор 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первого из них.

В приведенном выше примере была использована вторая форма оператора CASE.

Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора - COALESCE. Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE:

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCEможно переписать следующим образом:

SELECT DISTINCT product.model,

COALESCE(CAST(price as CHAR(20)),'Нет в наличии') price

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

WHERE product.type='pc';

Использование первой синтаксической формы оператора CASEможно продемонстрировать на следующем примере : Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели.

SELECT DISTINCT model, price,

CASE price WHEN (SELECT MAX(price) FROM pc) THEN 'Самый дорогой'

WHEN (SELECT MIN(price) FROM pc) THEN 'Самый дешевый'

ELSE 'Средняя цена' END comment

FROM pc ORDER BY price;

В результате выполнения запроса получим