Главная | Обратная связь | Поможем написать вашу работу!

Пример2: Определить средний год спуска на воду кораблей из таблицы Ships.

Запрос:

1. SELECT AVG(launched)

2. FROM Ships;

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

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

1. SELECT CAST(AVG(launched) AS NUMERIC(6,2))

2. FROM Ships;

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

1. SELECT AVG(CAST(launched AS NUMERIC(6,2)))

2. FROM Ships;

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

1. SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2)))AS NUMERIC(6,2))

2. FROM Ships;

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

1. SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2))

2. FROM Ships;

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

1. SELECT CONVERT(NUMERIC(6,2),AVG(launched*1.0))

2. FROM Ships;

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

CONVERT (<тип_данных[(<длина>)]>, <выражение> [, <стиль>])

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

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

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

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

	07/22/03
	03/07/22
	22/07/03
	2003-07-22 00:00:00.000

Есть одна особенность использования оператора CAST в SQL Server, связанная с преобразованием числа к его строковому представлению. Что произойдет, если число символов в числе превышает размер строки? Например,

SELECT CAST(1234.6 AS VARCHAR(5))

Следует ожидать, что мы получим сообщение об ошибке. Правильно, вот это сообщение:

Arithmetic overflow error converting numeric to data type varchar.

(«Ошибка арифметического переполнения при преобразовании числа к типу данных VARCHAR».)

Естественно, что мы будем ожидать того же сообщения и при выполнении следующего оператора:

SELECT CAST(123456 AS VARCHAR(5))

Но нет. В результате мы получим символ «*» вместо сообщения об ошибке. Мы не беремся судить, с чем это связано, однако, однажды мы столкнулись с проблемой диагностики ошибки в коде, в котором впоследствии выполнялось обратное преобразование к числовому типу.

В нашем простейшем примере это будет выглядеть так:

SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT)

Вот тут-то мы и получаем ошибку:

Syntax error converting the varchar value '*' to a column of data type int.

(«Ошибка синтаксиса при преобразовании значения «*» к типу данных INT».)

Оператор CASE

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

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

1. SELECT DISTINCT Product.model, price

2. FROM Product LEFT JOIN

3. PC ON Product.model = PC.model

4. WHERE product.type = 'pc'

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

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

1. SELECT DISTINCT product.model,

2. CASE

3. WHEN price IS NULL

4. THEN 'Нет в наличии'

5. ELSE CAST(price AS CHAR(20))

6. END price

7. FROM Product LEFT JOIN

8. PC ON Product.model = PC.model

9. WHERE product.type = 'pc'

Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст «Нет в наличии», в противном случае (ELSE) возвращается значение цены.

Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению.

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

1-я форма:

1. CASE <проверяемое выражение>

2. WHEN <сравниваемое выражение 1>

3. THEN <возвращаемое значение 1>

4. …

5. WHEN <сравниваемое выражение N>

6. THEN <возвращаемое значение N>

7. [ELSE <возвращаемое значение>]

8. END

2-я форма:

1. CASE

2. WHEN <предикат 1>

3. THEN <возвращаемое значение 1>

4. …

5. WHEN <предикат N>

6. THEN <возвращаемое значение N>

7. [ELSE <возвращаемое значение>]

8. END

Все предложения WHEN должны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL -значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них, так как остальные просто не будут проверяться.

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

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

1. CASE

2. WHEN A IS NOT NULL

3. THEN A

4. ELSE B

5. END

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

1. SELECT DISTINCT Product.model,

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

3. FROM Product LEFT JOIN

4. PC ON Product.model = PC.model

5. WHERE Product.type = 'pc';

Применение первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере.

⇐ Предыдущая 1 2 3 4 5 678 9 Следующая ⇒