Условие case в sql запросе

Условие case в sql запросе

В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в T-SQL при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int. Подробно о явном и неявном преобразовании типов в MS SQL Server можно прочитать в BOL.

Пример. Вывести среднюю цену ПК-блокнотов с предваряющим текстом "средняя цена = ".
Попытка выполнить запрос

SELECT ‘Средняя цена = ‘ + AVG(price) FROM laptop;

приведет к сообщению об ошибке

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

Это сообщение означает, что система не может выполнить неявное преобразование типа varchar к типу money. В подобных ситуациях может помочь явное преобразование типов. При этом, как указано в сообщении об ошибке, можно воспользоваться функцией CONVERT. Однако эта функция не стандартизована, поэтому в целях переносимости рекомендуется использовать стандартное выражение CAST. С него и начнем.
Если переписать наш запрос в виде

SELECT ‘Средняя цена = ‘ + CAST(AVG(price) AS CHAR(15)) FROM laptop;

в результате получим то, что требовалось:

Средняя цена = 1410.44

Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению. Синтаксис выражения CAST очень простой:

При этом следует иметь в виду, во-первых, что не любые преобразования типов возможны (стандарт содержит таблицу допустимых преобразований типов данных). Во-вторых, результат функции CAST для значения выражения, равного NULL, тоже будет NULL.
Рассмотрим еще один пример: определить средний год спуска на воду кораблей из таблицы Ships. Запрос

SELECT AVG(launched) FROM ships;

даст результат 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 от функции 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.

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

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 может быть использован в одной из двух синтаксических форм записи:

Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.
В приведенном выше примере была использована вторая форма оператора CASE.
Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора — COALESCE. Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE:

Базы данных

В этом учебном пособии вы узнаете, как использовать оператор CASE в Oracle/PLSQL c синтаксисом и примерами.

Описание

В Oracle/PLSQL оператор CASE имеет функциональность IF-THEN-ELSE. Начиная с Oracle 9i, вы можете использовать оператор CASE в SQL предложении.

Синтаксис

WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2

WHEN condition_n THEN result_n

Читайте также:  Установка драйверов с диска на windows 7

Параметры или аргументы

expression не является обязательным. Это значение, которое вы сравниваете с условиями (то есть: condition_1 , condition_2 … condition_n ).

condition_1 .. condition_n должны быть одного типа. Условия оцениваются по порядку, одно за другим. После того, как условие примет значение TRUE (истина), оператор CASE вернет результат, и не будет оценивать условия дальше.

result_1 .. result_n все должны быть одного типа данных. Это значение возвращается единожды, когда условие примет TRUE (истина).

Примечание

  • Если условие не примет TRUE, то оператор CASE вернет значение предложения ELSE.
  • Если предложение ELSE опущено и условие не примет TRUE, то оператор CASE вернет NULL.
  • Оператор CASE может иметь до 255 сравнений. Каждое предложение WHEN … THEN рассматривает 2 сравнения.

Применение

Оператор CASE можно использовать в следующих версиях Oracle / PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Пример

Вы можете использовать оператор CASE в SQL предложении следующим образом:

Подзапросы в выражении CASE

По материалам статьи Craig Freedman: Subqueries in CASE Expressions

В этой статье будет рассмотрено, как SQL Server обрабатывает подзапросы в выражении CASE. Кроме того, будут рассмотрены несколько экзотических возможностей соединений.

Для простых случаев использования выражений CASE (без подзапросов), возможна оценка только выражения CASE, состоящего из нескольких скалярных выражений:

Этот план запроса подразумевает просмотр таблицы T1 и оценку выражения CASE для каждой её строки. Оператор Compute Scalar вычисляет значение выражения CASE, включая оценку условия и принятие решения, будет ли выполняться оценка в предложении THEN или ELSE.
Если в выражение CASE поместить подзапросы, всё становится немного сложнее и существенно интересней.

Давайте сначала добавим к предложению WHEN простой подзапрос:

Как и для других EXISTS подзапросов, этот план использует левое полусоединение, позволяющее проверить, имеется ли для каждой строки в T1 соответствующая строка в T2. Однако, нормальное полусоединение (или анти-полусоединение) возвращает только парные строки (или непарные). В этом случае, должно быть возвращено хоть что-то (T1.b или T1.c) для каждой строки в T1. Мы не можем просто отказаться от строки T1 только потому, что для неё нет соответствующей строки в T2.
Решением стал специальный тип полусоединения со столбцом пробной таблицы. Это полусоединение возвращает все внешние соответствующие или не соответствующие строки, и устанавливает столбец пробной таблицы (в нашем случае это [Expr1010]) в истину или ложь, что указывает, была ли найдена соответствующая строка T1. После этого, выполняется оценка выражения CASE, для чего используется столбец пробной таблицы, с помощью которого определяется, какое значение будет возвращено.

Давайте теперь попробуем добавить к предложению THEN простой подзапрос:

Я добавил к T3 ограничение уникальности, позволяющее гарантировать, что скалярный подзапрос возвратит только одну строку. Без ограничения, план запроса был бы более сложен, поскольку оптимизатору нужно было бы гарантировать, что подзапрос действительно возвратит только одну строку, и ему пришлось бы выдавать ошибку, если бы вернулось больше одной строки.
Я также добавил в T1 ещё две строки, причём, условие в предложение WHEN выдаст ложь для первой строки и истину для второй строки. Таким образом, первая строка у нас будет подходить для ELSE, а вторая для THEN. Обратите внимание, что значение подзапроса в THEN будет использоваться, только если предложение WHEN будет истинно.
Ниже показан профиль статистики для плана исполнения этого запроса:

Этот план запроса использует специальный тип соединения вложенных циклов, в котором задействуется предикат PASSTHRU. Соединение оценивает предикат PASSTHRU для каждой внешней строки. Если предикат PASSTHRU оценивается как истина, соединение немедленно возвращает строку, подобную полусоединению или внешнему соединению. Если же предикат PASSTHRU оценивается как ложь, соединение выполняется обычным образом, т.е. выполняется попытка соединения внешней строки с внутренней строкой.
В показанном выше примере, предикат PASSTHRU выражения CASE является инверсией (обратите внимание на функцию IsFalseOrNull) предложения WHEN. Если предложение WHEN оценивается как истина, предикат PASSTHRU оценивается как ложь, происходит соединение, и поиск по внутренней части соединения выполняет оценку подзапроса THEN. Если предложение WHEN оценивается как ложь, предикаты PASSTHRU оценивается как истина, соединение пропускается, а поиск или подзапрос THEN не выполняется.
Обратите внимание, что просмотр T1 возвращает 2 строки, хотя поиск в T3 выполняется только один раз. Так происходит потому, что в нашем примере предложение WHEN истинно только для одной из двух строк. Предиката PASSTHRU является единственным механизмом, когда число строк на внешней стороне соединения вложенных циклов не соответствует в точности числу строк на внутренней стороне.
Также обратите внимание, что после того, как будет использовано внешнее соединение, невозможно гарантировать, что подзапрос в THEN вернёт хоть что-нибудь (в действительности гарантируется только то, что благодаря ограничению уникальности будет возвращено не более одной строки). Если подзапрос не возвращает строк, внешнее соединение просто возвратит NULL для T3.b. Если бы использовалось внутреннее соединение, отказаться от строки T1 было бы неправильно. Предостережение: я прогонял эти примеры на SQL Server 2005. Если Вы будете выполнять этот пример на SQL Server 2000, предикат PASSTHRU будет виден, но в плане исполнения запроса он появится как регулярный предикат предложения WHERE. К сожалению, для SQL Server 2000 не существует простого пути различения регулярных предикатов и предиката PASSTHRU.

Читайте также:  Как отправлять голосовые сообщения в дискорде

Предложение ELSE и несколько предложений WHEN

Подзапрос в предложении ELSE работает точно так же, как и подзапрос в предложении THEN. Для оценки условия подзапроса будет использован предикат PASSTHRU.
Точно так же выражение CASE с несколькими предложениями WHEN с подзапросами в каждом предложении THEN будет работать аналогичным образом. Отличие только в том, что предикатов PASSTHRU будет больше.
Например:

В этом плане запроса три соединения вложенных циклов с предикатами PASSTHRU. Для каждой строки T1, только один из трех предикатов PASSTHRU оценивается как истина, и только один из трех подзапросов будет выполнен. Обратите внимание, что пока второе предложение WHEN соответствует "T1.b > 0", это значит, что первое предложение WHEN, где "T1.a > 0" оказалось ложным. Это также относится и к предложению ELSE. Таким образом, предикаты PASSTHRU для второго и третьего подзапроса включают проверку "T1.a > 0 OR…".

Столбец пробной таблицы в качестве предиката PASSTHRU

Наконец, давайте рассмотрим запрос с подзапросами в предложениях WHEN и в предложениях THEN. Также, для разнообразия, давайте переместим выражение CASE из списка SELECT в предложение WHERE.

В этом плане исполнения запроса имеется левое полусоединение со столбцом пробной таблицы, позволяющее оценить подзапрос в предложении WHEN, и соединение вложенных циклов с предикатом PASSTHRU для столбца пробной таблицы, позволяющее решить, выполнять ли оценку подзапроса в предложении THEN. Поскольку выражение CASE было перемещено в предложение WHERE, для оценки выходных значений из списка SELECT вместо оператора Compute Scalar используется оператор Filter, с помощью которого определяется, какие строки будут возвращены. Все остальное работает точно так же.

В следующей статье, я рассмотрю несколько других типов подзапросов.

Ссылка на основную публикацию
Удаление последнего элемента списка
Введение. Основные операции О дносвязный список – структура данных, в которой каждый элемент (узел) хранит информацию, а также ссылку на...
Телефон самсунг с хорошей камерой недорогой
Если вы ищете лучший телефон Samsung, тогда рейтинг поможет разобраться в их различиях. Посмотрите какой смартфон лучшие купить из всех...
Телефон перестал заряжаться быстрой зарядкой
Наверняка многие сталкивались с тем, что смартфон ни с того ни с сего перестаёт заряжаться. Другая распространённая беда — слишком...
Удаление дубликатов фотографий на русском бесплатно
Здравствуйте Уважаемый Друг. У каждого из нас на компьютере хранится большое количество различных фотографий изображений и тому подобных картинок. Парой...
Adblock detector