Оператор CASE и вложенный случай в SQL Server: пример T-SQL

Обзор Кейса в реальной жизни!

Опять же, в реальной жизни мы совершаем разные действия в зависимости от исхода различных условий. Для большей наглядности рассмотрим пример ниже:

• Если авиабилеты будут стоить меньше 100 долларов, то я поеду в Лос-Анджелес.
• Если авиабилеты будут стоить от 100 до 200 долларов, то я поеду в Нью-Йорк.
• Если авиабилеты будут стоить от 200 до 400 долларов, то я поеду в Европу.
• В противном случае я предпочту посетить близлежащее туристическое место.

Рассмотрим, в отличие от приведенного выше примера, разделение условия и действия на отдельные категории:

В приведенном выше примере мы видим, что результат различных условий определяет отдельное действие. Например, посетитель совершит поездку в Нью-Йорк только при условии, что стоимость авиабилета составляет от 100 до 200 долларов. Аналогично, MS SQL Оператор CASE также предоставляет возможность выполнять различные операторы T-SQL в зависимости от результатов различных условий.

Что такое оператор CASE в SQL Server?

Заявление CASE в SQL Server является расширением ЕСЛИ ЕЩЕ Оператор CASE. В отличие от IF…ELSE, где допускается максимум одно условие, CASE позволяет пользователю применять несколько условий для выполнения различных наборов действий в MS SQL. Он возвращает соответствующее значение, связанное с условием, определенным пользователем.

Давайте научимся использовать оператор CASE в SQL и его концепцию в следующих разделах. В 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 обозначает выражение, которое в конечном итоге будет сравниваться со значениями Value_1, Value_2 и так далее.
• Параметры Statement_1, Statement_2… обозначают операторы, которые будут выполнены, если Case_Expression = Value_1, Case_Expression = Value_2 и так далее.
• Вкратце, условие заключается в том, равно ли Case_Expression значению Value_N, а действие — в выполнении Statement_N, если указанный выше результат истинен.
• Параметр ALIAS_NAME является необязательным и представляет собой псевдоним, присваиваемый результату оператора CASE в SQL Server. Он чаще всего используется, когда мы применяем CASE в предложении SELECT SQL Server.

Правила для простого случая

• В режиме Simple Case допускается только проверка равенства выражения Case_Expression со значениями от Value_1 до Value_N.
• Выражение Case_Expression сравнивается со значением в порядке возрастания, начиная с первого значения, то есть 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 не соответствует ни одному из значений, отображается NULL.

Приведенная ниже диаграмма иллюстрирует последовательность выполнения простого кейса:

Примеры

Предположим, у нас есть таблица с именем 'Guru'99' с двумя столбцами, Tutorial_ID и Tutorial_name, и четырьмя строками, как показано ниже. Мы будем использовать этот 'GuruТаблица 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: SIMPLE CASE с опцией 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

Результат: Приведенная ниже диаграмма объясняет последовательность выполнения оператора SIMPLE CASE с оператором 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… обозначает выражение, которое будет оцениваться как TRUE или FALSE.
• Параметры Statement_1, Statement_2… обозначают операторы, которые будут выполнены, если соответствующий результат Boolean_Expression_1, Boolean_Expression_2 равен TRUE.
• Вкратце, условие — это логическое выражение Boolean_Expression_1…, а действие — выполнение оператора Statement_N, если указанное выше логическое выражение Boolean_Expression_1 истинно.
• Параметр ALIAS_NAME является необязательным и представляет собой псевдоним, присваиваемый результату оператора CASE. Он чаще всего используется, когда мы применяем CASE в предложении SELECT.

Правила для разыскиваемого дела

• В отличие от простого случая, случай поиска не ограничивается только проверкой на равенство, а допускает использование логических выражений.
• Логическое выражение вычисляется по порядку, начиная с первого логического выражения, т.е. Boolean_Expression_1. Ниже описан подход к выполнению:
• Если Boolean_Expression_1 имеет значение TRUE, то дальнейшие операторы WHEN…THEN пропускаются, и выполнение CASE немедленно завершается.
• Если Boolean_Expression_1 равно FALSE, то Boolean_Expression_2 проверяется на истинность. Этот процесс проверки логических выражений продолжается до тех пор, пока одно из логических выражений не вернет 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

Результат: На приведенной ниже диаграмме показана последовательность выполнения запроса SEARCHED CASE без каких-либо дополнительных условий.

Запрос 2: Поиск по типу Case с опцией 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

Результат: На приведенной ниже диаграмме показана последовательность выполнения запроса SEARCHED CASE с оператором ELSE.

Разница между подходами к выполнению: SIMPLE и SEARCH CASE

Рассмотрим пример SIMPLE CASE ниже:

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

Здесь 'Tutorial_name' является частью выражения CASE в SQL. Затем значение 'Tutorial_name' сравнивается с каждым значением WHEN, т.е. 'SQL'… до тех пор, пока 'Tutorial_name' не совпадет со значением WHEN.

Напротив, в примере SEARCH CASE нет выражения 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

Здесь каждое выражение WHEN имеет собственное условное логическое выражение. Каждое логическое выражение, например, Tutorial_name = 'SQL'…, оценивается на TRUE/FALSE до тех пор, пока первое логическое выражение не окажется TRUE.

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

Вложенный 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 Server ложно, то выполняется оператор ELSE. Часть ELSE содержит вложенный оператор CASE в SQL. В зависимости от стоимости авиабилета отображается один из следующих результатов:

• Если стоимость авиабилетов превышает 400 долларов, система выводит сообщение «Посетите близлежащее туристическое место».
• Система выводит надпись «Посетите Лос-Анджелес», если стоимость авиабилетов находится в диапазоне от 0 до 100 долларов.
• Система выводит надпись «Посетите Нью-Йорк», если стоимость авиабилетов находится в диапазоне от 101 до 200 долларов.
• Система выводит надпись «Посетите Европу», если стоимость авиабилетов составляет от 201 до 400 долларов.

Приведённый ниже результат показывает местоположение, возвращаемое при выполнении вложенного оператора CASE внутри ветви ELSE:

Вложенный CASE: CASE внутри CASE

В SQL можно использовать оператор CASE внутри другого оператора CASE. Ниже приведён пример кода 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. Возвращаемое местоположение соответствует тем же диапазонам цен билетов, что и в предыдущем примере, поэтому билет за $250 попадает в диапазон $201–$400 и возвращает «Visit Europe».

В приведенном ниже результате показано местоположение, возвращенное внутренним запросом CASE для билета стоимостью 250 долларов:

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

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

Мы можем использовать 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 на 'Structured Query language'.
• Если Tutorial_Name = 'PL/SQL', то измените Tutorial_Name на 'Oracle PL/SQL'.
• Если Tutorial_Name = 'MSSQL', то измените Tutorial_Name на 'Microsoft SQL'.
• Если Tutorial_Name = 'Hadoop', то измените Tutorial_Name на 'Apache Hadoop'.

Выполнение запроса обновляет соответствующие строки, как показано ниже:

Давайте зададим вопрос о 'GuruИспользуйте таблицу 99 для проверки обновленных значений:

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

Мы можем использовать оператор 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 BY. Параметр @Order установлен на 1, и поскольку первое логическое выражение WHEN оценивается как TRUE, для условия ORDER BY выбирается Tutorial_ID. Результат показан ниже:

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

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

Часто задаваемые вопросы (FAQ)