Выражение CASE – условный оператор языка SQL

Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.

Оператор CASE имеет 2 формы:

Первая форма:	Вторая форма:
CASE WHEN условие_1 THEN возвращаемое_значение_1 … WHEN условие_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END	CASE проверяемое_значение WHEN сравниваемое_значение_1 THEN возвращаемое_значение_1 … WHEN сравниваемое_значение_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END

В качестве значений здесь могут выступать и выражения.

Разберем на примере первую форму CASE:

SELECT

ID,Name,Salary,

 

CASE

WHEN Salary>=3000 THEN 'ЗП >= 3000'

WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'

ELSE 'ЗП < 2000'

END SalaryTypeWithELSE,

 

CASE

WHEN Salary>=3000 THEN 'ЗП >= 3000'

WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'

END SalaryTypeWithoutELSE

 

FROM Employees

 

ID	Name	Salary	SalaryTypeWithELSE	SalaryTypeWithoutELSE
1000	Иванов И.И.	5000	ЗП >= 3000	ЗП >= 3000
1001	Петров П.П.	1500	ЗП < 2000	NULL
1002	Сидоров С.С.	2500	2000 <= ЗП < 3000	2000 <= ЗП < 3000
1003	Андреев А.А.	2000	2000 <= ЗП < 3000	2000 <= ЗП < 3000
1004	Николаев Н.Н.	1500	ЗП < 2000	NULL
1005	Александров А.А.	2000	2000 <= ЗП < 3000	2000 <= ЗП < 3000

WHEN-условия проверяются последовательно, сверху-вниз. При достижении первого удовлетворяющего условия дальнейшая проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.

Если ни одно из WHEN-условий не выполняется, то возвращается значение, указанное после слова ELSE (что в данном случае означает «ИНАЧЕ ВЕРНИ …»).

Если ELSE-блок не указан и не выполняется ни одно WHEN-условие, то возвращается NULL.

И в первой, и во второй форме ELSE-блок идет в самом конце конструкции CASE, т.е. после всех WHEN-условий.

Разберем на примере вторую форму CASE:

Допустим, на новый год решили премировать всех сотрудников и попросили вычислить сумму бонусов по следующей схеме:

·Сотрудникам ИТ-отдела выдать по 15% от ЗП;

·Сотрудникам Бухгалтерии по 10% от ЗП;

·Всем остальным по 5% от ЗП.

 

Используем для данной задачи запрос с выражением CASE:

SELECT

ID,Name,Salary,DepartmentID,

 

-- для наглядности выведем процент в виде строки

CASE DepartmentID -- проверяемое значение

WHEN 2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам

ELSE '5%' -- всем остальным по 5%

END NewYearBonusPercent,

 

-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса

Salary/100*

CASE DepartmentID

WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusAmount

 

FROM Employees

 

ID	Name	Salary	DepartmentID	NewYearBonusPercent	BonusAmount
1000	Иванов И.И.	5000	1	5%	250
1001	Петров П.П.	1500	3	15%	225
1002	Сидоров С.С.	2500	2	10%	250
1003	Андреев А.А.	2000	3	15%	300
1004	Николаев Н.Н.	1500	3	15%	225
1005	Александров А.А.	2000	NULL	5%	100

Здесь делается последовательная проверка значения DepartmentID с WHEN-значениями. При достижении первого равенства DepartmentID с WHEN-значением, проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.

Соответственно, значение блока ELSE возвращается в случае, если DepartmentID не совпал ни с одним WHEN-значением.

Если блок ELSE отсутствует, то в случае несовпадения DepartmentID ни с одним WHEN-значением будет возвращено NULL.

Вторую форму CASE несложно представить при помощи первой формы:

SELECT

ID,Name,Salary,DepartmentID,

 

CASE

WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам

WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам

ELSE '5%' -- всем остальным по 5%

END NewYearBonusPercent,

 

-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса

Salary/100*

CASE

WHEN DepartmentID=2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN DepartmentID=3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusAmount

 

FROM Employees

 

Так что, вторая форма – это всего лишь упрощенная запись для тех случаев, когда нам нужно сделать сравнение на равенство, одного и того же проверяемого значения с каждым WHEN-значением/выражением.

Примечание. Первая и вторая форма CASE входят в стандарт языка SQL, поэтому скорее всего они должны быть применимы во многих СУБД.

 

С MS SQL версии 2012 появилась упрощенная форма записи IIF. Она может использоваться для упрощенной записи конструкции CASE, в том случае если возвращаются только 2 значения. Конструкция IIF имеет следующий вид:

IIF(условие, true_значение, false_значение)

 

Т.е. по сути это обертка для следующей CASE конструкции:

CASE WHEN условие THEN true_значение ELSE false_значение END

 

Посмотрим на примере:

SELECT

ID,Name,Salary,

 

IIF(Salary>=2500,'ЗП >= 2500','ЗП < 2500') DemoIIF,

 

CASE WHEN Salary>=2500 THEN 'ЗП >= 2500' ELSE 'ЗП < 2500' END DemoCASE

 

FROM Employees

 

Конструкции CASE, IIF могут быть вложенными друг в друга. Рассмотрим абстрактный пример:

SELECT

ID,Name,Salary,

 

CASE

WHEN DepartmentID IN(1,2) THEN 'A'

WHEN DepartmentID=3 THEN

                     CASE PositionID -- вложенный CASE

                       WHEN 3 THEN 'B-1'

                       WHEN 4 THEN 'B-2'

                     END

ELSE 'C'

END Demo1,

 

IIF(DepartmentID IN(1,2),'A',

IIF(DepartmentID=3,CASE PositionID WHEN 3 THEN 'B-1' WHEN 4 THEN 'B-2' END,'C')) Demo2

 

FROM Employees

 

Так как конструкция CASE и IIF представляют из себя выражение, которые возвращают результат, то мы можем использовать их не только в блоке SELECT, но и в остальных блоках, допускающих использование выражений, например, в блоках WHERE или ORDER BY.

Для примера, пускай перед нами поставили задачу – создать список на выдачу ЗП на руки, следующим образом:

·Первым делом ЗП должны получить сотрудники у кого оклад меньше 2500

·Те сотрудники у кого оклад больше или равен 2500, получают ЗП во вторую очередь

·Внутри этих двух групп нужно упорядочить строки по ФИО (поле Name)

 

Попробуем решить эту задачу при помощи добавления CASE-выражение в блок ORDER BY:

SELECT

ID,Name,Salary

FROM Employees

ORDER BY

CASE WHEN Salary>=2500 THEN 1 ELSE 0 END, -- выдать ЗП сначала тем у кого она ниже 2500

Name -- дальше упорядочить список в порядке ФИО

 

ID	Name	Salary
1005	Александров А.А.	2000
1003	Андреев А.А.	2000
1004	Николаев Н.Н.	1500
1001	Петров П.П.	1500
1000	Иванов И.И.	5000
1002	Сидоров С.С.	2500

Как видим, Иванов и Сидоров уйдут с работы последними.

И абстрактный пример использования CASE в блоке WHERE:

SELECT

ID,Name,Salary

FROM Employees

WHERE CASE WHEN Salary>=2500 THEN 1 ELSE 0 END=1 -- все записи у которых выражение равно 1

 

Можете попытаться самостоятельно переделать 2 последних примера с функцией IIF.

И напоследок, вспомним еще раз о NULL-значениях:

SELECT

ID,Name,Salary,DepartmentID,

 

CASE

WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам

WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам

WHEN DepartmentID IS NULL THEN '-' -- внештатникам бонусов не даем (используем IS NULL)

ELSE '5%' -- всем остальным по 5%

END NewYearBonusPercent1,

 

-- а так проверять на NULL нельзя, вспоминаем что говорилось про NULL во второй части

CASE DepartmentID -- проверяемое значение

WHEN 2 THEN '10%'

WHEN 3 THEN '15%'

WHEN NULL THEN '-' --!!! в данном случае использование второй формы CASE не подходит

ELSE '5%'

END NewYearBonusPercent2

 

FROM Employees

 

ID	Name	Salary	DepartmentID	NewYearBonusPercent1	NewYearBonusPercent2
1000	Иванов И.И.	5000	1	5%	5%
1001	Петров П.П.	1500	3	15%	15%
1002	Сидоров С.С.	2500	2	10%	10%
1003	Андреев А.А.	2000	3	15%	15%
1004	Николаев Н.Н.	1500	3	15%	15%
1005	Александров А.А.	2000	NULL	-	5%

Конечно можно было переписать и как-то так:

SELECT

ID,Name,Salary,DepartmentID,

 

CASE ISNULL(DepartmentID,-1) -- используем замену в случае NULL на -1

WHEN 2 THEN '10%'

WHEN 3 THEN '15%'

WHEN -1 THEN '-' -- если мы уверены, что отдела с ID равным (-1) нет и не будет

ELSE '5%'

END NewYearBonusPercent3

 

FROM Employees

 

В общем, полет фантазии в данном случае не ограничен.

Для примера посмотрим, как при помощи CASE и IIF можно смоделировать функцию ISNULL:

SELECT

ID,Name,LastName,

 

ISNULL(LastName,'Не указано') DemoISNULL,

CASE WHEN LastName IS NULL THEN 'Не указано' ELSE LastName END DemoCASE,

IIF(LastName IS NULL,'Не указано',LastName) DemoIIF

FROM Employees

 

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

Агрегатные функции

Здесь мы рассмотрим только основные и наиболее часто используемые агрегатные функции:

Название	Описание
COUNT(*)	Возвращает количество строк полученных оператором «SELECT … WHERE …». В случае отсутствии WHERE, количество всех записей таблицы.
COUNT(столбец/выражение)	Возвращает количество значений (не равных NULL), в указанном столбце/выражении
COUNT(DISTINCT столбец/выражение)	Возвращает количество уникальных значений, не равных NULL в указанном столбце/выражении
SUM(столбец/выражение)	Возвращает сумму по значениям столбца/выражения
AVG(столбец/выражение)	Возвращает среднее значение по значениям столбца/выражения. NULL значения для подсчета не учитываются.
MIN(столбец/выражение)	Возвращает минимальное значение по значениям столбца/выражения
MAX(столбец/выражение)	Возвращает максимальное значение по значениям столбца/выражения

Агрегатные функции позволяют нам сделать расчет итогового значения для набора строк полученных при помощи оператора SELECT.

Рассмотрим каждую функцию на примере:

SELECT

COUNT(*) [Общее кол-во сотрудников],

COUNT(DISTINCT DepartmentID) [Число уникальных отделов],

COUNT(DISTINCT PositionID) [Число уникальных должностей],

COUNT(BonusPercent) [Кол-во сотрудников у которых указан % бонуса],

MAX(BonusPercent) [Максимальный процент бонуса],

MIN(BonusPercent) [Минимальный процент бонуса],

SUM(Salary/100*BonusPercent) [Сумма всех бонусов],

AVG(Salary/100*BonusPercent) [Средний размер бонуса],

AVG(Salary) [Средний размер ЗП]

FROM Employees

 

Общее кол-во сотрудников	Число уникальных отделов	Число уникальных должностей	Кол-во сотрудников у которых указан % бонуса	Максимальный процент бонуса	Минимальный процент бонуса	Сумма всех бонусов	Средний размер бонуса	Средний размер ЗП
6	3	4	3	50	15	3325	1108.33333333333	2416.66666666667

Для большей наглядности я решил здесь сделать исключение и воспользовался синтаксисом […] для задания псевдонимов колонок.