Программа с использование case

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.

Выражение CASE имеет два формата:

• Простое CASE выражение сравнивает выражение с набором простых выражений, чтобы определить результат.
• Искомое CASE выражение вычисляет набор логических выражений для определения результата.

Оба формата поддерживают дополнительный аргумент ELSE.

CASE может использоваться в любом операторе или предложении, допускающих допустимое выражение. Например, можно использовать CASE в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как , IN, WHERE, ORDER BY и HAVING.

Синтаксис

Синтаксис для SQL Server, базы данных Azure SQL и Azure Synapse Analytics.

— Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END — Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END

Синтаксис для параллельных Data Warehouse.

Обучение работе с 1с и другие новости.

CASE WHEN when_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

input_expression

Выражение, вычисляемое при использовании простого CASE формата. input_expression — это любое допустимое выражение.

WHEN when_expression

Простое выражение, с которым сравнивается input_expression при использовании простого CASE формата. when_expression — это любое допустимое выражение. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.

THEN result_expression

Выражение, возвращаемое, если input_expression равно when_expression , принимает значение TRUE или Boolean_expression значение TRUE. result expression — это любое допустимое выражение.

ELSE else_result_expression

Выражение возвращается, если ни операция сравнения не имеет значения TRUE. Если этот аргумент опущен, а операция сравнения не имеет значения TRUE, CASE возвращает значение NULL. else_result_expression — это любое допустимое выражение. Типы данных аргумента else_result_expression и каждого из выражений result_expression должны быть одинаковыми или неявно приводимыми друг к другу.

WHEN Boolean_expression

Логическое выражение, вычисляемое при использовании искомого CASE формата. Boolean_expression — это любое допустимое логическое выражение.

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Типы возвращаемых данных

Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Case Management Software fully built on Microsoft

Возвращаемые значения

Простое выражение CASE

Простое CASE выражение выполняется путем сравнения первого выражения с выражением в каждом предложении WHEN для эквивалентности. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.

• Допускается только проверка равенства.
• В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.
• Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.
• Если ни одна из операций input_expression = when_expression не дает значения TRUE, Компонент SQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Поисковое выражение CASE

• Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.
• Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.
• Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Remarks

SQL Server допускает только 10 уровней вложенности в CASE выражениях.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL).

Выражение CASE оценивает свои условия последовательно и останавливается с первым условием, условие которого выполняется. В некоторых ситуациях выражение вычисляется до того, как CASE выражение получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегированные выражения, отображаемые в параметре WHEN, CASE сначала вычисляются, а затем предоставляются выражению CASE . Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. Это происходит перед вычислением CASE выражения.

WITH Data (value) AS ( SELECT 0 UNION ALL SELECT 1 ) SELECT CASE WHEN MIN(value) = 100 THEN 1 END FROM Data; GO

Вы должны зависеть только от порядка вычисления условий WHEN для скалярных выражений (включая несоотносимые вложенные запросы, возвращающие скаляры), а не для агрегированных выражений.

Кроме того, необходимо убедиться, что по крайней мере одно из выражений в предложениях THEN или ELSE не является константой NULL. Хотя значение NULL может быть возвращено из нескольких результирующих выражений, не все из них могут явно быть константой NULL. Если во всех результирующих выражениях используется константа NULL, возвращается ошибка 8133.

Примеры

A. Использование инструкции SELECT с простым выражением CASE

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.

USE AdventureWorks2019; GO SELECT ProductNumber, Category = CASE ProductLine WHEN ‘R’ THEN ‘Road’ WHEN ‘M’ THEN ‘Mountain’ WHEN ‘T’ THEN ‘Touring’ WHEN ‘S’ THEN ‘Other sale items’ ELSE ‘Not for sale’ END, Name FROM Production.Product ORDER BY ProductNumber; GO

Б. Использование инструкции SELECT с искомыми выражениями CASE

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.

USE AdventureWorks2019; GO SELECT ProductNumber, Name, «Price Range» = CASE WHEN ListPrice = 0 THEN ‘Mfg item — not for resale’ WHEN ListPrice < 50 THEN ‘Under $50’ WHEN ListPrice >= 50 AND ListPrice < 250 THEN ‘Under $250’ WHEN ListPrice >= 250 AND ListPrice < 1000 THEN ‘Under $1000’ ELSE ‘Over $1000’ END FROM Production.Product ORDER BY ProductNumber; GO

В. Использование CASE в предложении ORDER BY

В следующих примерах выражение используется CASE в предложении ORDER BY для определения порядка сортировки строк на основе заданного значения столбца. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee . Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке BusinessEntityID (по убыванию). Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке BusinessEntityID (по возрастанию). Во втором примере результирующий набор упорядочивается по столбцу TerritoryName , если столбец CountryRegionName содержит значение «США», и по столбцу CountryRegionName в остальных строках.

SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC, CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; GO
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN ‘United States’ THEN TerritoryName ELSE CountryRegionName END; GO

Г. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции CASE UPDATE используется для определения значения, заданного для столбца VacationHours для сотрудников с SalariedFlag значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.

USE AdventureWorks2019; GO UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN ((VacationHours — 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0; GO

Д. Использование CASE в инструкции SET

В следующем примере выражение используется CASE в инструкции SET в функции dbo.GetContactInfo с табличным значением . В базе данных AdventureWorks2022 все данные, связанные с людьми, хранятся в таблице Person.Person . Например, человек может быть сотрудником, представителем поставщика или заказчиком. Функция возвращает имя и фамилию заданного BusinessEntityID пользователя, а также тип контакта для этого человека. Выражение CASE в инструкции SET определяет значение, отображаемое для столбца ContactType , на основе наличия столбца в Employee таблицах BusinessEntityID , Vendor или Customer .

Е. Использование CASE в предложении HAVING

В следующем примере выражение в предложении CASE HAVING используется для ограничения строк, возвращаемых инструкцией SELECT. Оператор возвращает почасовую ставку для каждой должности HumanResources.Employee в таблице. Предложение HAVING ограничивает названия теми, которые удерживаются наемными работниками с максимальной ставкой заработной платы более 40 долларов, или неоплачиваемых сотрудников с максимальной ставкой заработной платы более 15 долларов.

USE AdventureWorks2019; GO SELECT JobTitle, MAX(ph1.Rate) AS MaximumRate FROM HumanResources.Employee AS e INNER JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING ( MAX(CASE WHEN SalariedFlag = 1 THEN ph1.Rate ELSE NULL END) > 40.00 OR MAX(CASE WHEN SalariedFlag = 0 THEN ph1.Rate ELSE NULL END) > 15.00 ) ORDER BY MaximumRate DESC; GO

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Ж. Использование инструкции SELECT с выражением CASE

В инструкции CASE SELECT выражение позволяет заменять значения в результирующем наборе на основе значений сравнения. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. Если значение не существует, отображается текст «Не продается».

— Uses AdventureWorks SELECT ProductAlternateKey, Category = CASE ProductLine WHEN ‘R’ THEN ‘Road’ WHEN ‘M’ THEN ‘Mountain’ WHEN ‘T’ THEN ‘Touring’ WHEN ‘S’ THEN ‘Other sale items’ ELSE ‘Not for sale’ END, EnglishProductName FROM dbo.DimProduct ORDER BY ProductKey; GO

H. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции CASE UPDATE используется для определения значения, заданного для столбца VacationHours для сотрудников с SalariedFlag значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов.

— Uses AdventureWorks UPDATE dbo.DimEmployee SET VacationHours = ( CASE WHEN ((VacationHours — 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) WHERE SalariedFlag = 0; GO

См. также раздел

• Выражения (Transact-SQL)
• SELECT (Transact-SQL)
• COALESCE (Transact-SQL)
• Предложение IIF (Transact-SQL)
• CHOOSE (Transact-SQL)

Источник: learn.microsoft.com

Программа с использование case

Конструкция switch/case оценивает некоторое выражение и сравнивает его значение с набором значений. И при совпадении значений выполняет определенный код.:

Конструкция switch имеет следующее формальное определение:

switch (выражение) < case значение1: код,выполняемый если выражение имеет значение1 break; case значение2: код,выполняемый если выражение имеет значение1 break; //. case значениеN: код, выполняемый если выражение имеет значениеN break; default: код, выполняемый если выражение не имеет ни одно из выше указанных значений break; >

После ключевого слова switch в скобках идет сравниваемое выражение. Значение этого выражения последовательно сравнивается со значениями, помещенными после оператора сase . И если совпадение будет найдено, то будет выполняться определенный блок сase .

В конце каждого блока сase должен ставиться один из операторов перехода: break , goto case , return или throw . Как правило, используется оператор break . При его применении другие блоки case выполняться не будут.

string name = «Tom»; switch (name)

В данном случае конструкция switch последовательно сравнивает значение переменной name с набором значений, которые указаны после операторов case . Поскольку здесь значение переменной name — строка «Tom», то будет выполняться блок

case «Tom»: Console.WriteLine(«Ваше имя — Tom»); break;

Соответственно мы увидим на консоли

Ваше имя — Tom

Если значение переменной name не совпадает ни с каким значением после операторов case , то ни один из блоков case не выполняется. Однако если даже в этом случае нам все равно надо выполнить какие-нибудь действия, то мы можем добавить в конструкцию switch необязательный блок default . Например:

string name = «Alex»; switch (name)

В данном случае никакое из значений после операторов case не совпадает со значением переменной name, поэтому будет выполняться блок default:

default: Console.WriteLine(«Неизвестное имя»); break;

Однако если мы хотим, чтобы, наоборот, после выполнения текущего блока case выполнялся другой блок case, то мы можем использовать вместо break оператор goto case :

int number = 1; switch (number) < case 1: Console.WriteLine(«case 1»); goto case 5; // переход к case 5 case 3: Console.WriteLine(«case 3»); break; case 5: Console.WriteLine(«case 5»); break; default: Console.WriteLine(«default»); break; >

Возвращение значения из switch

Конструкция switch позволяет возвращать некоторое значение. Для возвращения значения в блоках case может применятся оператор return . Например, определим следующий метод:

int DoOperation(int op, int a, int b) < switch (op) < case 1: return a + b; case 2: return a — b; case 3: return a * b; default: return 0; >>

В метод DoOperation() передается числовой код операции и два операнда. В зависимости от кода операции над операндами выполнется определенная операция и ее результат возвращается из метода. Для примера при по умолчанию из метода возвращается 0, если код операции не равен 1, 2 или 3.

Затем мы можем вызвать этот метод:

int DoOperation(int op, int a, int b) < switch (op) < case 1: return a + b; case 2: return a — b; case 3: return a * b; default: return 0; >> int result1 = DoOperation(1, 10, 5); // 15 Console.WriteLine(result1); // 15 int result2 = DoOperation(3, 10, 5); // 50 Console.WriteLine(result2); // 50

Получение результата из switch

Хотя конструкция switch в примере выше прекрасно работает, тем не менее мы ее можем сократить и получить результат неосредственно из конструкции switch :

int DoOperation(int op, int a, int b) < int result = op switch < 1 =>a + b, 2 => a — b, 3 => a * b, _ => 0 >; return result; >

Теперь не требуется оператор case , а после сравниваемого значения ставится оператор стрелка => . Значение справа от стрелки выступает в качестве возвращаемоего значения. Кроме того, вместо оператора default используется почерк _. В итоге результат конструкции switch будет присвиваиваться переменной result.

Естестввенно, мы можем сразу возвратить из метода результат без присвоения переменной результата конструкции switch:

int DoOperation(int op, int a, int b) < return op switch < 1 =>a + b, 2 => a — b, 3 => a * b, _ => 0 >; >

Или сделать метод еще короче:

int DoOperation(int op, int a, int b) => op switch < 1 =>a + b, 2 => a — b, 3 => a * b, _ => 0 >;

Обращаю внимание, что данное упрощение касается лишь таких конструкций switch , которые возвращают некоторые значения, как в примере выше.

Стоит отметить, что при возвращении значения из метода, метод должен в любом случае возвращать значение. Например, следующая версия метода не будет работать

int DoOperation(int op, int a, int b) < return op switch < 1 =>a + b, 2 => a — b, 3 => a * b >; >

Эта версия метода возвращает значение, если код операции равен 1, 2 или 3. Но что, если будет передано значение 4 или какое-то другое? Поэтому данная версия метода даже не скомпилируется. Поэтому нам надо предусмотреть возвращение значения из метода при всех возможных вариантах. То есть, мы можем, как в примере выше, добавить в конструкцию switch блок default, в котором будет возвращаться значение при всех остальных случаях.

Источник: metanit.com

Выражение CASE в SQL: объяснение на примерах

Перевод статьи «SQL Case Statement Tutorial – With When-Then Clause Example Queries».

Выражение СASE — это, по сути, SQL-версия условной логики. Это выражение может использоваться примерно так же, как if -предложения в языках программирования вроде JavaScript, хотя его структура немного отличается.

Данные для примера

Представьте, что вы преподаете литературу в школе. ваши ученики должны написать сочинение.

Вы создали следующую таблицу, чтобы отслеживать, кто из учеников уже сдал сочинение (там же проставляются оценки). Если ученик еще не сдал сочинение, в графе оценок значится NULL.

STUDENT_IDNAMESUBMITTED_ESSAYGRADE

1	Джон	TRUE	86
2	Саид	TRUE	90
3	Алиса	FALSE	NULL
4	Ной	TRUE	68
5	Элеанор	TRUE	95
6	Акико	FALSE	NULL
7	Отто	TRUE	76
8	Джамал	TRUE	85
9	Кьяра	TRUE	88
10	Клементина	FALSE	NULL

Как написать выражение CASE в SQL

Допустим, вы хотели бы выводить ученикам сообщения о том, сдали они сочинение или нет. Чтобы получить статус каждого ученика, вы можете просто выбрать столбец submitted_essay , но тогда в сообщении будет только TRUE или FALSE, а это не очень читабельно.

Вместо этого вы можете использовать выражение CASE и вывоить разные сообщения, основываясь на статусе в submitted_essay.

Базовая структура выражения CASE :

CASE WHEN. THEN. END

Использование CASE WHEN , THEN и END является обязательным, а ELSE и AS — опцональным. Выражение СASE должно идти внутри инструкции SELECT.

SELECT name, CASE WHEN submitted_essay IS TRUE THEN ‘сочинение сдано!’ ELSE ‘сдай сочинение!’ END AS status FROM students;

В приведенном выше примере мы выбрали имена учеников, а затем вывели разные сообщения в столбце status, основываясь на значении submitted_essay. Результирующая таблица выглядит так:

NAMESTATUS

Акико	сдай сочинение!
Клементина	сдай сочинение!
Алиса	сдай сочинение!
Саид	сочинение сдано!
Элеанор	сочинение сдано!
Отто	сочинение сдано!
Ной	сочинение сдано!
Кьяра	сочинение сдано!
Джон	сочинение сдано!
Джамал	сочинение сдано!

Усложняем пример

Идем дальше. Допустим, вы хотели бы включить в сообщение побольше информации. Если ученик уже сдал сочинение, вы хотели бы добавить в сообщение его оценку, а если нет — напомнить, что сочинение еще не сдано. Здесь нам пригодятся предложения WHEN/THEN .

SELECT name, essay_grade, CASE WHEN essay_grade >= 80 THEN ‘молодец’ WHEN essay_grade < 80 THEN ‘можешь лучше’ ELSE ‘сдай сочинение!’ END AS teacher_comment FROM students;

В этом примере кода мы вывели имена учеников, их оценки, а также комментарии, соответствующие оценкам.

После первого предложения WHEN/THEN вы можете добавить сколько угодно других WHEN/THEN , а также предложение ELSE, покрывающее все неучтенные случаи. Это аналог логики if. else if. else в JavaScript (или if. elif. else в Python и т. д.).

Обратите внимание, что в этом случае предложение ELSE призвано захватить все сочинения с оценками NULL (т. е. еще не сданные сочинения), но в других ситуациях для проверки, является ли значение null, вы могли бы использовать IS NULL.

Не забывайте ставить END в конце вашего CASE -выражения!

В таблице представлены результаты этого запроса:

NAMEESSAY_GRADETEACHER_COMMENT

Акико	NULL	сдай сочинение!
Клементина	NULL	сдай сочинение!
Алиса	NULL	сдай сочинение!
Саид	90	молодец
Элеанор	95	молодец
Отто	76	можешь лучше
Ной	68	можешь лучше
Кьяра	88	молодец
Джон	86	молодец
Джамал	85	молодец

Выражения CASE легко понять и изучить. Их применение — это лаконичный способ внести ясность в ваши SQL-запросы.

Источник: techrocks.ru