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

Преглед на случая в реалния живот!

Отново, в реалния живот, ние извършваме различни действия в зависимост от резултата от различни условия. За да разясним по-подробно, разгледайте примера по-долу:

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

Нека разгледаме категоризирането на условието и действието поотделно от горния пример:

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

Какво е CASE Statement в SQL Server?

CASE Изявление в SQL Server е продължението на АКО... ИНАЧЕ оператор. За разлика от IF…ELSE, където е разрешено максимум едно условие, CASE позволява на потребителя да прилага множество условия за изпълнение на различни набори от действия в MS SQL. Връща съответстваща стойност, свързана с условието, дефинирано от потребителя.

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

Правила за прост случай

• 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.

Диаграмата по-долу илюстрира потока на изпълнение на прост случай:

Примери

Да предположим, че имаме таблица с име „Guru99' с две колони, Tutorial_ID и Tutorial_name, и четири реда, както е показано по-долу. Ще използваме това 'Guru99' таблица в следващите примери.

Заявка 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

Синтаксисът за търсен регистър

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.
• Накратко, условието е Булев_израз_1… и действието е изпълнението на Statement_N, ако горният Булев_израз_1 е TRUE.
• ALIAS_NAME е незадължителен и е псевдонимът, даден на резултата от оператора CASE. Използва се най-вече, когато използваме CASE в клаузата SELECT.

Правила за претърсен случай

• За разлика от простия случай, търсеният случай не е ограничен само до проверка за равенство, а позволява и булев израз.
• Булевият израз се изчислява по ред, започвайки от първия булев израз, т.е. Булев_Израз_1. По-долу е показан подходът към изпълнението:
• Ако Boolean_Expression_1 е TRUE, тогава допълнителните WHEN…THEN оператори се пропускат и изпълнението на CASE ще END незабавно.
• Ако Булев_израз_1 е НЕВЯРЕН (FALSE), тогава Булев_израз_2 се оценява за условие ИСТИНСКО (TRUE). Този процес на оценяване на булевия израз продължава, докато един от булевите изрази не върне ИСТИНСКО (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

Резултат: Диаграмата по-долу обяснява потока на изпълнение на SEARCHED CASE с ELSE.

Разлика между подхода за изпълнение: 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

Тук „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 оператор. Декларираме a променлив за самолетния билет и разклонението му върху неговата стойност. По-долу е даден примерният 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 е False, тогава се изпълнява операторът ELSE. Частта ELSE съдържа вложен оператор CASE в SQL. В зависимост от стойността на самолетния билет се показва един от следните резултати:

• Системата отпечатва „Посетете близко туристическо място“, ако самолетните билети са > $400.
• Системата отпечатва „Посетете Лос Анджелис“, ако самолетните билети са МЕЖДУ $0 И $100.
• Системата отпечатва „Посетете Ню Йорк“, ако самолетните билети са МЕЖДУ $101 И $200.
• Системата отпечатва „Visit Europe“, ако самолетните билети са МЕЖДУ $201 И $400.

Резултатът по-долу показва местоположението, върнато, когато вложеният CASE се изпълни в клона ELSE:

Вложен 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. Върнатото местоположение следва същите диапазони на билетите като предишния пример, така че билет от $250 попада в диапазона $201–$400 и връща „Visit Europe“.

Резултатът по-долу показва местоположението, върнато от вътрешния CASE за билет от $250:

CASE с АКТУАЛИЗАЦИЯ

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

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

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

Въпроси и Отговори