Інструкція 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 <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, Регістр_виразу = Значення_2, … і так далі.
У двох словах, стан чи є Case_Expression = Value_N і ACTION є виконанням Statement_N, якщо наведений вище результат ІСТИННИЙ.
ALIAS_NAME є необов’язковим і є псевдонімом, наданим результату оператора CASE SQL Server. Здебільшого використовується, коли ми використовуємо регістр у пропозиції вибору SQL-сервера.

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

Простий регістр дозволяє лише перевіряти рівність Case_Expression із Value_1 до Value_N.
Case_Expression порівнюється зі значенням у порядку, починаючи з першого значення, тобто Value_1. Нижче наведено підхід до виконання:
Якщо Case_Expression еквівалентний Value_1, тоді подальші оператори WHEN…THEN пропускаються, і виконання CASE негайно ЗАВЕРШАЄТЬСЯ.
Якщо Case_Expression не збігається зі значенням_1, тоді Case_Expression порівнюється зі значенням_2 для еквівалентності. Цей процес порівняння Case_Expression зі значенням триватиме, доки Case_Expression не знайде відповідне еквівалентне значення з набору Value_1, Value_2,…
Якщо нічого не збігається, керування переходить до оператора ELSE, і Statement_Else буде виконано.
ELSE необов’язковий.
Якщо ELSE немає і Case_Expression не відповідає жодному зі значень, тоді Буде відображено Null.

Наведена нижче схема ілюструє послідовність виконання Simple Case.

прикладів

Припущення: Припустимо, що ми маємо таблицю як "Guru99" з двома стовпцями та чотирма рядками, як показано нижче:

Ми будемо використовувати "Guru99" у наступних прикладах

Запит 1: SIMPLE CASE з опцією 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

Синтаксис для пошукового регістру

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.
Коротше кажучи, Condition – це Boolean_Expression_1,… і ACTION – це виконання Statement_N, якщо вище boolean_Expression_1 TRUE.
ALIAS_NAME є необов’язковим і є псевдонімом, наданим результату оператора CASE. Здебільшого використовується, коли ми використовуємо CASE у реченні select.

Правила розшукової справи

На відміну від простого випадку, 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 відсутній і жоден із Boolean_expression не повертає 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 РОЗШУКУВАНИЙ СПРАВА з ІНШЕ.

Різниця між підходом виконання: SIMPLE і SEARCH 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

Тут, "Назва_підручника" є частиною виразу CASE в SQL. Потім "Назва_підручника" значення порівнюється з кожним WHEN значення, тобто «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

Тут кожен оператор WHEN має свої Умовний логічний вираз. Кожен Логічний вираз тобто Tutorial_name = 'SQL',… оцінюється для ІСТИНА/НЕПРАВДА до перший логічний вираз, який оцінюється як ІСТИНА.

Різниця між простим і пошуковим відмінками

Простий випадок	Розшукана справа
За ключовим словом CASE безпосередньо слід CASE_Expression і перед оператором WHEN. Наприклад: СПРАВА WHEN Значення_1 THEN Оператор_1…	За ключовим словом Case слідує оператор WHEN, а між CASE і WHEN немає виразу. Наприклад: ВИПАДОК КОЛИ ПОТІМ Заява_1…
У простому випадку VALUE існує для кожного оператора WHEN. Ці значення: Value_1, Value_2… Порівнюються з одним CASE_Expression послідовно. Результат отримує оцінку для умови TRUE/FALSE для кожного оператора WHEN. Наприклад: СПРАВА WHEN Значення_1 THEN Оператор_1… WHEN Значення_2 THEN Оператор_2…	У Searched Case Boolean_Expression існує для кожного оператора WHEN. Цей Boolean_Expressions: 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 і введено умову 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 $

CASE з UPDATE

Припущення: припустимо, що у нас є таблиця «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 до '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'

Давайте запитаємо таблицю 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 By.

@Order встановлено на 1, і як перший, КОЛИ логічний вираз обчислюється як TRUE, Tutorial_ID вибрано для Order by Condition

Цікаві факти!

CASE може бути вкладений в інший CASE, а також в інший оператор IF…ELSE.
Окрім SELECT, CASE можна використовувати з іншим SQL речення типу UPDATE, ORDER BY.

Підсумки

У MS SQL існує два типи CASE: простий CASE та пошуковий CASE
ELSE є необов’язковим у операторі CASE.

Огляд справи в реальному житті!

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

СТАТТІ ПО ТЕМІ

Простий ВИПАДОК

Синтаксис простого регістру

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

прикладів

Шукав CASE

Правила розшукової справи

прикладів

Різниця між підходом виконання: SIMPLE і SEARCH CASE.

Різниця між простим і пошуковим відмінками

Вкладений CASE: CASE у IF ELSE

Вкладений CASE: CASE всередині CASE

CASE з UPDATE

КЕЙС з Замовити по

Цікаві факти!

Підсумки

Підпишіться на розсилку