Оператор 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.

  1. Простой СЛУЧАЙ
  2. Поиск ДЕЛА

Простой СЛУЧАЙ

Синтаксис простого случая

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 не соответствует ни одному из значений, тогда Будет отображаться ноль.

На диаграмме ниже показан процесс выполнения простого случая.

Работа с простым оператором Case
Работа с простым оператором Case

Примеры

Предположение: Предположим, что у нас есть таблица в виде 'Гуру99' с двумя столбцами и четырьмя строками, как показано ниже:

Простой случай в SQL Server

Мы будем использовать 'Гуру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

Результат: На диаграмме ниже поясняется последовательность выполнения ПРОСТОГО СЛУЧАЯ, БЕЗ БОЛЬШОГО.

Простой случай в SQL Server

Запрос 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.

Простой случай в SQL Server

Поиск ДЕЛА

Синтаксис искомого случая

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

Результат: Ниже диаграмма объясняет ход выполнения ПОИСК ДЕЛА НЕТ БОЛЬШЕ.

Пример поиска CASE в SQL Server

Запрос 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 РАЗЫСКАННОЕ ДЕЛО ЕЩЕ.

Поиск CASE в SQL Server

Разница между подходом к исполнению: ПРОСТОЙ и ПОИСКОВЫЙ СЛУЧАЙ.

Давайте посмотрим на ПРОСТОЙ СЛУЧАЙ пример ниже:

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.

Например:
СЛУЧАЙ
КОГДА Значение_1 ТОГДА Оператор_1…

За ключевым словом Case следует оператор WHEN, и между CASE и WHEN нет выражения.

Например:
СЛУЧАЙ, КОГДА ТОГДА Утверждение_1…

В простом случае VALUE существует для каждого оператора WHEN. Эти значения: Value_1, Value_2… последовательно сравниваются с одним CASE_Expression. Результат оценивается на предмет условия TRUE/FALSE для каждого оператора WHEN.

Например:
СЛУЧАЙ
КОГДА Значение_1 ТОГДА Оператор_1…
КОГДА Значение_2 ТОГДА Оператор_2…

В случае поиска Boolean_Expression существует для каждого оператора WHEN. Эти логические_выражения: Boolean_Expression_1, Boolean_Expression_2,… оценивают условие TRUE/FALSE для каждого оператора WHEN.

Например:
Кейсы
КОГДА ТОГДА Утверждение_1…
КОГДА ТОГДА Утверждение_2…

Простой случай поддерживает только проверку равенства. Т.е. является ли CASE_Expression = VALUE_1, VALUE_2…

Например:
СЛУЧАЙ WHEN Value_1 THEN Statement_1… В приведенном выше примере единственная операция, выполняемая системой, — это проверка того, имеет ли Case_Expression = Value_1.

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

Например:
СЛУЧАЙ, КОГДА THEN Statement_1… В приведенном выше примере Boolean_Expression_1 может содержать операторы «равно» и «не равно», например A = B, A != B.

Вложенный 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 в SQL Server

Вложенный 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 долларов США.

Пример вложенного CASE в SQL Server

ДЕЛО с ОБНОВЛЕНИЕМ

Предположение: предположим, что у нас есть таблица «Guru99» с двумя столбцами и четырьмя строками, как показано ниже:

СЛУЧАЙ с ОБНОВЛЕНИЕМ в SQL Server

В дальнейших примерах мы будем использовать таблицу 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'.

СЛУЧАЙ с ОБНОВЛЕНИЕМ в SQL Server

Давайте запросим таблицу Guru99, чтобы проверить обновленное значение:

СЛУЧАЙ с ОБНОВЛЕНИЕМ в SQL Server

СЛУЧАЙ с заказом по

Мы можем использовать 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 выбирается для заказа по условию.

СЛУЧАЙ с упорядочиванием по SQL Server

Интересные факты!

  • CASE может быть вложен в другой оператор CASE, а также в другой оператор IF…ELSE.
  • Помимо SELECT, CASE можно использовать с другим SQL такое предложение, как UPDATE, ORDER BY.

Итого

  • В MS SQL существует два типа CASE: простой CASE и CASE с поиском.
  • ELSE не является обязательным в операторе CASE.