Автоматическое растормаживание колес: Тормозные устройства колес предназначены для уменьшения длины пробега и улучшения маневрирования ВС при...
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
Топ:
Отражение на счетах бухгалтерского учета процесса приобретения: Процесс заготовления представляет систему экономических событий, включающих приобретение организацией у поставщиков сырья...
Методика измерений сопротивления растеканию тока анодного заземления: Анодный заземлитель (анод) – проводник, погруженный в электролитическую среду (грунт, раствор электролита) и подключенный к положительному...
Особенности труда и отдыха в условиях низких температур: К работам при низких температурах на открытом воздухе и в не отапливаемых помещениях допускаются лица не моложе 18 лет, прошедшие...
Интересное:
Уполаживание и террасирование склонов: Если глубина оврага более 5 м необходимо устройство берм. Варианты использования оврагов для градостроительных целей...
Распространение рака на другие отдаленные от желудка органы: Характерных симптомов рака желудка не существует. Выраженные симптомы появляются, когда опухоль...
Отражение на счетах бухгалтерского учета процесса приобретения: Процесс заготовления представляет систему экономических событий, включающих приобретение организацией у поставщиков сырья...
Дисциплины:
2020-05-08 | 237 |
5.00
из
|
Заказать работу |
|
|
Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.
Оператор 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 |
Для большей наглядности я решил здесь сделать исключение и воспользовался синтаксисом […] для задания псевдонимов колонок.
|
|
Эмиссия газов от очистных сооружений канализации: В последние годы внимание мирового сообщества сосредоточено на экологических проблемах...
Таксономические единицы (категории) растений: Каждая система классификации состоит из определённых соподчиненных друг другу...
Кормораздатчик мобильный электрифицированный: схема и процесс работы устройства...
Индивидуальные очистные сооружения: К классу индивидуальных очистных сооружений относят сооружения, пропускная способность которых...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!