Пример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 приводит к четырехзначному отображению года.
Есть одна особенность использования оператора 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 можно продемонстрировать на следующем примере.
Читайте также: CREATE TABLE имя_таблицы Воспользуйтесь поиском по сайту: ![]() ©2015 - 2025 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...
|