Оператор CASE и вложенный случай в SQL Server: пример T-SQL
Обзор Кейса в реальной жизни!
Опять же, в реальной жизни мы совершаем разные действия в зависимости от исхода разных условий.
Чтобы уточнить более подробную информацию, рассмотрим пример ниже:
- Если авиабилеты будут стоить меньше 100 долларов, то я поеду в Лос-Анджелес.
- Если авиабилеты будут стоить от 100 до 200 долларов, я поеду в Нью-Йорк.
- Если авиабилеты будут стоить от 200 до 400 долларов, то я поеду в Европу.
- В противном случае я предпочту посетить какое-нибудь близлежащее туристическое место.
Давайте рассмотрим категоризацию состояний и действий отдельно от приведенного выше примера ниже:
Условия - Авиабилеты | Выполненные действия, только если Условие ИСТИНА |
Less чем $ 100 | Посетите Лос-Анджелес |
От $ 100 до $ 200 | Посетите Нью-Йорк |
От $ 200 до $ 400 | Посетите Европу |
Ни одно из вышеперечисленных условий не выполнено | Рядом туристическое место |
В приведенном выше примере мы видим, что результат различных условий управляет отдельным действием. Например, Посетитель выполнит действие посещения Нью-Йорка только в том случае, если стоимость авиабилета составляет от 100 до 200 долларов США.
Аналогичным образом, оператор MS SQL CASE также предоставляет возможность выполнять различные инструкции T-SQL в зависимости от результатов различных условий.
Что такое оператор CASE в SQL Server?
Оператор CASE в SQL Server является расширением оператора IF…ELSE. В отличие от IF…ELSE, где допускается только одно условие, CASE позволяет пользователю применять несколько условий для выполнения разных наборов действий в MS SQL. Он возвращает соответствующее значение, связанное с условием, определенным пользователем.
Давайте узнаем, как использовать Case в SQL, и его концепцию в следующих разделах.
In MS SQL, существует два типа CASE.
- Простой СЛУЧАЙ
- Поиск ДЕЛА
Простой СЛУЧАЙ
Синтаксис простого случая
CASE <Case_Expression> WHEN Value_1 THEN Statement_1 WHEN Value_2 THEN Statement_2 . . WHEN Value_N THEN Statement_N [ELSE Statement_Else] END AS [ALIAS_NAME]
Здесь,
- Параметр Case_Expression обозначает выражение, с которым мы в конечном итоге будем сравниваться Значение_1, Значение_2, ...
- Параметры Заявление_1, Заявление_2… обозначают операторы, которые будут выполняться, если Case_Expression = Значение_1, Case_Expression = Значение_2, … и так далее.
- В двух словах, состояние является ли Case_Expression = Value_N и ACTION — это выполнение Statement_N, если приведенный выше результат ВЕРНЫЙ.
- ПСЕВДОНИМ является необязательным и представляет собой псевдоним, присвоенный результату инструкции SQL Server CASE. Чаще всего используется, когда мы используем Case в предложении выбора SQL-сервера.
Правила для простого случая
- Простой регистр позволяет проверять только равенство Case_Expression со Value_1 и Value_N.
- Case_Expression сравнивается со Value, начиная с первого значения, т. е. Value_1. Ниже приведен подход к исполнению:
- Если Case_Expression эквивалентно Value_1, то дальнейшие инструкции WHEN…THEN пропускаются, и выполнение CASE немедленно ЗАКОНЧИТСЯ.
- Если Case_Expression не совпадает со Value_1, то Case_Expression сравнивается со Value_2 на предмет эквивалентности. Этот процесс сравнения Case_Expression со значением будет продолжаться до тех пор, пока Case_Expression не найдет соответствующее эквивалентное значение из набора Value_1, Value_2,…
- Если ничего не совпадает, управление переходит к оператору ELSE, и Statement_Else будет выполнен.
- ELSE не является обязательным.
- Если ELSE отсутствует и Case_Expression не соответствует ни одному из значений, тогда Будет отображаться ноль.
На диаграмме ниже показан процесс выполнения простого случая.
Примеры
Предположение: Предположим, что у нас есть таблица в виде 'Гуру99' с двумя столбцами и четырьмя строками, как показано ниже:
Мы будем использовать 'Гуру99' таблица в дальнейших примерах
Запрос 1: ПРОСТОЙ СЛУЧАЙ с опцией NO ELSE
SELECT Tutorial_ID, Tutorial_name, CASE Tutorial_name WHEN 'SQL' THEN 'SQL is developed by IBM' WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' END AS Description FROM Guru99
Результат: На диаграмме ниже поясняется последовательность выполнения ПРОСТОГО СЛУЧАЯ, БЕЗ БОЛЬШОГО.
Запрос 2: ПРОСТОЙ СЛУЧАЙ с опцией ELSE.
SELECT Tutorial_ID, Tutorial_name, CASE Tutorial_name WHEN 'SQL' THEN 'SQL is developed by IBM' WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' ELSE 'This is NO SQL language.' END AS Description FROM Guru99
Результат: На диаграмме ниже поясняется поток выполнения ПРОСТОГО СЛУЧАЯ с ELSE.
Поиск ДЕЛА
Синтаксис искомого случая
CASE WHEN <Boolean_Expression_1> THEN Statement_1 WHEN <Boolean_Expression_2> THEN Statement_2 . . WHEN <Boolean_Expression_N> THEN Statement_N [ELSE Statement_Else] END AS [ALIAS_NAME]
Здесь,
- Параметр Boolean_Expression_1, … обозначает выражение, которое будет оценено как ИСТИНА или ЛОЖЬ.
- Параметры Statement_1, Statement_2… обозначают операторы, которые будут выполняться, если соответствующий результат Boolean_Expression_1, Boolean_Expression_2 равен TRUE.
- Вкратце, условие — это логическое_выражение_1,… а ДЕЙСТВИЕ — это выполнение оператора_N, если вышеприведенное логическое_выражение_1 имеет значение ИСТИНА.
- ALIAS_NAME является необязательным и представляет собой псевдоним, присвоенный результату оператора CASE. Чаще всего используется, когда мы используем CASE в предложении выбора.
Правила для разыскиваемого дела
- В отличие от простого случая, Searched Case не ограничивается только проверкой равенства, но допускает логические выражения.
- Логическое выражение вычисляется в порядке, начиная с первого логического выражения, т.е. Boolean_expression_1. Ниже приведен подход к исполнению:
- Если Boolean_expression_1 имеет значение TRUE, то дальнейшие инструкции WHEN…THEN пропускаются и выполнение CASE немедленно ЗАВЕРШАЕТСЯ.
- Если Boolean_expression_1 имеет значение FALSE, тогда Boolean_expression_2 оценивается как условие TRUE. Этот процесс оценки Boolean_expression будет продолжаться до тех пор, пока одно из Boolean_expression не вернет TRUE.
- Если ничего не совпадает, управление переходит к оператору ELSE, и Statement_Else будет выполнен.
- Как и простой регистр, ELSE также не является обязательным в случае поиска.
- Если ELSE отсутствует и ни одно из логических_выражений не возвращает TRUE, тогда будет отображаться значение Null.
Ниже диаграммы проиллюстрировать поток выполнения Разыскиваемое дело.
Примеры
Запрос 1: ПОИСК СЛУЧАЯ с опцией NO ELSE
SELECT Tutorial_ID, Tutorial_name, CASE WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM' WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' END AS Description FROM Guru99
Результат: Ниже диаграмма объясняет ход выполнения ПОИСК ДЕЛА НЕТ БОЛЬШЕ.
Запрос 2: ИСКАННЫЙ СЛУЧАЙ с ELSE опцию.
SELECT Tutorial_ID, Tutorial_name, CASE WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM' WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' ELSE 'This is NO SQL language.' END AS Description FROM Guru99
Результат: Ниже диаграмма объясняет ход выполнения of РАЗЫСКАННОЕ ДЕЛО ЕЩЕ.
Разница между подходом к исполнению: ПРОСТОЙ и ПОИСКОВЫЙ СЛУЧАЙ.
Давайте посмотрим на ПРОСТОЙ СЛУЧАЙ пример ниже:
SELECT Tutorial_ID, Tutorial_name, CASE Tutorial_name WHEN 'SQL' THEN 'SQL is developed by IBM' WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' ELSE 'This is NO SQL language.' END AS Description FROM Guru99
Здесь, 'Название_учебника' является частью выражения CASE в SQL. Затем 'Название_учебника' значение сравнивается с каждым КОГДА ценности, т.е. «SQL»… до тех пор, пока «Tutorial_name» не будет соответствовать значениям WHEN.
Напротив, ПОИСК ДЕЛА пример не имеет CASE-выражение:
SELECT Tutorial_ID, Tutorial_name, CASE WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM' WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.' WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.' END AS Description FROM Guru99
Здесь каждый КОГДА заявление имеет свои Условное логическое выражение. Каждый Логическое выражение т.е. Tutorial_name = 'SQL',… оценивается для ИСТИНА / ЛОЖЬ до первое логическое значение выражение, оценивающее ИСТИНА.
Разница между простым и поисковым случаем
Простой случай | Разыскиваемое дело |
---|---|
За ключевым словом CASE следует CASE_Expression и перед оператором WHEN.
Например: |
За ключевым словом Case следует оператор WHEN, и между CASE и WHEN нет выражения.
Например: |
В простом случае VALUE существует для каждого оператора WHEN. Эти значения: Value_1, Value_2… последовательно сравниваются с одним CASE_Expression. Результат оценивается на предмет условия TRUE/FALSE для каждого оператора WHEN.
Например: |
В случае поиска Boolean_Expression существует для каждого оператора WHEN. Эти логические_выражения: Boolean_Expression_1, Boolean_Expression_2,… оценивают условие TRUE/FALSE для каждого оператора WHEN.
Например: |
Простой случай поддерживает только проверку равенства. Т.е. является ли CASE_Expression = VALUE_1, VALUE_2…
Например: |
С Boolean_Expression_N вариант поиска поддерживает любую операцию, результатом которой является логическое значение. Он включает в себя операторы равно и не равно.
Например: |
Вложенный CASE: CASE в IF ELSE
Мы можем использовать CASE внутри IF ELSE. Ниже приведен пример кода MS-SQL.
DECLARE @Flight_Ticket int; SET @Flight_Ticket = 190; IF @Flight_Ticket > 400 PRINT 'Visit Nearby Tourist Location'; ELSE BEGIN SELECT CASE WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles' WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York' WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe' END AS Location END
В приведенном выше примере CASE вложен в оператор IF…ELSE:
Сначала будет выполнен оператор IF, и если условие Case в SQL сервер имеет значение False, то будет выполнен оператор ELSE.
В противном случае внутри него содержится вложенный оператор CASE в SQL. В зависимости от стоимости билета на рейс будет отображен один из следующих результатов:
- Система напечатает «Посетить близлежащее туристическое место», если авиабилеты стоят > 400 долларов США.
- Система напечатает «Посетите Лос-Анджелес», если билеты на самолет стоят от 0 до 100 долларов США.
- Система напечатает «Посетите Нью-Йорк», если билеты на самолет стоят ОТ 101 ДО 200 долларов США.
- Система напечатает «Посетите Европу», если билеты на самолет стоят ОТ 201 ДО 400 долларов США.
Вложенный CASE: CASE внутри CASE
Мы можем использовать CASE внутри CASE в SQL. Ниже приведен пример кода MS-SQL.
DECLARE @Flight_Ticket int; SET @Flight_Ticket = 250; SELECT CASE WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.' WHEN @Flight_Ticket < 400 THEN CASE WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles' WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York' WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe' END END AS Location
В приведенном выше примере CASE вложен в другой оператор CASE:
Система начинается с выполнения внешнего CASE. Если Flight_Ticket < $400, будет выполнен внутренний CASE.
В зависимости от стоимости авиабилета будет отображен один из следующих результатов:
- Система напечатает «Посетить близлежащее туристическое место», если авиабилеты стоят > 400 долларов США.
- Система напечатает «Посетите Лос-Анджелес», если билеты на самолет стоят от 0 до 100 долларов США.
- Система напечатает «Посетите Нью-Йорк», если билеты на самолет стоят ОТ 101 ДО 200 долларов США.
- Система напечатает «Посетите Европу», если билеты на самолет стоят ОТ 201 ДО 400 долларов США.
ДЕЛО с ОБНОВЛЕНИЕМ
Предположение: предположим, что у нас есть таблица «Guru99» с двумя столбцами и четырьмя строками, как показано ниже:
В дальнейших примерах мы будем использовать таблицу Guru99.
Мы можем использовать CASE с UPDATE. Ниже приведен пример кода MS-SQL:
UPDATE Guru99 SET Tutorial_Name = ( CASE WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.' WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL' WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.' WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.' END )
В приведенном выше примере CASE используется в операторе UPDATE.
В зависимости от значения Tutorial_Name столбец Tutorial_Name получит обновление со значением оператора THEN.
- Если Tutorial_Name = 'SQL', ТОГДА обновите Tutorial_Name до «Язык структурированных запросов».
- Если Tutorial_Name = 'PL/SQL', ТОГДА обновите Tutorial_Name до 'Oracle PL/SQL'
- Если Tutorial_Name = ‘MSSQL’, ТОГДА обновите Tutorial_Name до ‘Microsoft SQL'
- Если Tutorial_Name = 'Hadoop', ТОГДА обновите Tutorial_Name до 'Apache Hadoop'.
Давайте запросим таблицу Guru99, чтобы проверить обновленное значение:
СЛУЧАЙ с заказом по
Мы можем использовать CASE с Order By. Ниже приведен пример кода MS-SQL:
Declare @Order Int; Set @Order = 1 Select * from Guru99 order by CASE WHEN @Order = 1 THEN Tutorial_ID WHEN @Order = 2 THEN Tutorial_Name END DESC
Здесь CASE используется с заказом по.
@Order имеет значение 1, и сначала КОГДА логическое выражение принимает значение TRUE, Tutorial_ID выбирается для заказа по условию.
Интересные факты!
- CASE может быть вложен в другой оператор CASE, а также в другой оператор IF…ELSE.
- Помимо SELECT, CASE можно использовать с другим SQL такое предложение, как UPDATE, ORDER BY.
Итого
- В MS SQL существует два типа CASE: простой CASE и CASE с поиском.
- ELSE не является обязательным в операторе CASE.