Select case when then else: SQL-запросы. Условный оператор CASE…WHEN…THEN

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть третья / Хабр

Предыдущие части

О чем будет рассказано в этой части

1. с выражением CASE, которое позволяет включить условные выражения в запрос;
2. с агрегатными функциями, которые позволяют получить разного рода итоги (агрегированные значения) рассчитанные на основании детальных данных, полученных оператором «SELECT … WHERE …»;
3. с предложением GROUP BY, которое в скупе с агрегатными функциями позволяет получить итоги по детальным данным в разрезе групп;
4. с предложением HAVING, которое позволяет произвести фильтрацию по сгруппированным данным.

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

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

Разберем на примере первую форму 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

Если 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
 

Соответственно, значение блока 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 -- дальше упорядочить список в порядке ФИО
 

И абстрактный пример использования 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
 

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-конструкцией нам еще пригодится, поэтому в этой части в первую очередь внимание уделено именно ей.

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

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

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

Разберем каким образом получилось каждое возвращенное значение, а за одно вспомним конструкции базового синтаксиса оператора SELECT.

Во-первых, т.к. мы в запросе не указали WHERE-условия, то итоги будут считаться для детальных данных, которые получаются запросом:

SELECT * FROM Employees

т.е. для всех строк таблицы Employees.

Для наглядности выберем только поля и выражения, которые используются в агрегатных функциях:

SELECT
  DepartmentID,
  PositionID,
  BonusPercent,
  Salary/100*BonusPercent [Salary/100*BonusPercent],
  Salary
FROM Employees

Теперь разберем каждое агрегированное значение:

SELECT * FROM Employees

SELECT DISTINCT DepartmentID -- 2. берем только уникальные значения
FROM Employees
WHERE DepartmentID IS NOT NULL -- 1. отбрасываем NULL значения

SELECT BonusPercent -- 2. берем все значения
FROM Employees
WHERE BonusPercent IS NOT NULL -- 1. отбрасываем NULL значения

SELECT
  COUNT(*), -- 6
  COUNT(DISTINCT DepartmentID), -- 3
  COUNT(DepartmentID) -- 5
FROM Employees

SELECT TOP 1 BonusPercent
FROM Employees
WHERE BonusPercent IS NOT NULL
ORDER BY BonusPercent DESC -- сортируем по убыванию

SELECT TOP 1 BonusPercent
FROM Employees
WHERE BonusPercent IS NOT NULL
ORDER BY BonusPercent -- сортируем по возрастанию

SELECT Salary/100*BonusPercent
FROM Employees
WHERE Salary/100*BonusPercent IS NOT NULL

SELECT
  AVG(Salary/100*BonusPercent), -- 1108.33333333333
  SUM(Salary/100*BonusPercent)/COUNT(Salary/100*BonusPercent), -- 1108.33333333333
  SUM(Salary/100*BonusPercent)/COUNT(*) -- 554.166666666667
FROM Employees

SELECT
  AVG(ISNULL(Salary/100*BonusPercent,0)), -- 554.166666666667
  SUM(Salary/100*BonusPercent)/COUNT(*) -- 554.166666666667
FROM Employees

• COUNT(*) – служит для подсчета общего количества строк, которые получены оператором «SELECT … WHERE …»
• во всех остальных вышеперечисленных агрегатных функциях при расчете итога, NULL-значения не учитываются
• если нам нужно учесть все строки, это больше актуально для функции AVG, то предварительно необходимо осуществить обработку NULL значений, например, как было показано выше «AVG(ISNULL(Salary,0))»

Соответственно при задании с агрегатными функциями дополнительного условия в блоке WHERE, будут подсчитаны только итоги, по строкам удовлетворяющих условию. Т.е. расчет агрегатных значений происходит для итогового набора, который получен при помощи конструкции 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
WHERE DepartmentID=3 -- учесть только ИТ-отдел

SELECT
  DepartmentID,
  PositionID,
  BonusPercent,
  Salary/100*BonusPercent [Salary/100*BonusPercent],
  Salary
FROM Employees
WHERE DepartmentID=3 -- учесть только ИТ-отдел

Идем, дальше. В случае, если агрегатная функция возвращает NULL (например, у всех сотрудников не указано значение Salary), или в выборку не попало ни одной записи, а в отчете, для такого случая нам нужно показать 0, то функцией ISNULL можно обернуть агрегатное выражение:

SELECT
  SUM(Salary),
  AVG(Salary),

  -- обрабатываем итог при помощи ISNULL
  ISNULL(SUM(Salary),0),
  ISNULL(AVG(Salary),0)
FROM Employees
WHERE DepartmentID=10 -- здесь специально указан несуществующий отдел, чтобы запрос не вернул записей

Я считаю, что очень важно понимать назначение каждой агрегатной функции и то каким образом они производят расчет, т.к. в SQL это главный инструмент, который служит для расчета итоговых значений.

В данном случае мы рассмотрели, как каждая агрегатная функция ведет себя самостоятельно, т.е. она применялась к значениям всего набора записей полученным командой SELECT. Дальше мы рассмотрим, как эти же функции применяются для вычисления итогов по группам, при помощи конструкции GROUP BY.

GROUP BY – группировка данных

SELECT
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные только по ИТ отделу

А теперь представьте, что нас попросили получить такие же цифры в разрезе каждого отдела. Конечно мы можем засучить рукава и выполнить этот же запрос для каждого отдела. Итак, сказано-сделано, пишем 4 запроса:

SELECT
  'Администрация' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=1 -- данные по Администрации

SELECT
  'Бухгалтерия' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=2 -- данные по Бухгалтерии

SELECT
  'ИТ' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные по ИТ отделу

SELECT
  'Прочие' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID IS NULL -- и еще не забываем данные по внештатникам

В результате мы получим 4 набора данных:

Обратите внимание, что мы можем использовать поля, заданные в виде констант – ‘Администрация’, ‘Бухгалтерия’, …

В общем все цифры, о которых нас просили, мы добыли, объединяем все в Excel и отдаем директору.

Отчет директору понравился, и он говорит: «а добавьте еще колонку с информацией по среднему окладу». И как всегда это нужно сделать очень срочно.

Мда, что делать?! Вдобавок представим еще что отделов у нас не 3, а 15.

Вот как раз то примерно для таких случаев служит конструкция GROUP BY:

SELECT
  DepartmentID,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees
GROUP BY DepartmentID

Пока не обращайте внимание, на то что департаменты у нас вывелись в виде цифр, дальше мы научимся выводить все красиво.

В предложении GROUP BY можно указывать несколько полей «GROUP BY поле1, поле2, …, полеN», в этом случае группировка произойдет по группам, которые образовывают значения данных полей «поле1, поле2, …, полеN».

Для примера, сделаем группировку данных в разрезе Отделов и Должностей:

SELECT
  DepartmentID,PositionID,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID,PositionID

Давайте, теперь на этом примере, попробуем разобраться как работает GROUP BY

Для полей, перечисленных после GROUP BY из таблицы Employees определяются все уникальные комбинации по значениям DepartmentID и PositionID, т.е. происходит примерно следующее:

SELECT DISTINCT DepartmentID,PositionID
FROM Employees

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID IS NULL AND PositionID IS NULL

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID=1 AND PositionID=2

-- ...

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID=3 AND PositionID=4

А потом все эти результаты объединяются вместе и отдаются нам в виде одного набора:

Из основного, стоит отметить, что в случае группировки (GROUP BY), в перечне колонок в блоке SELECT:

• Мы можем использовать только колонки, перечисленные в блоке GROUP BY
• Можно использовать выражения с полями из блока GROUP BY
• Можно использовать константы, т.к. они не влияют на результат группировки
• Все остальные поля (не перечисленные в блоке GROUP BY) можно использовать только с агрегатными функциями (COUNT, SUM, MIN, MAX, …)
• Не обязательно перечислять все колонки из блока GROUP BY в списке колонок SELECT

И демонстрация всего сказанного:

SELECT
  'Строка константа' Const1, -- константа в виде строки
  1 Const2, -- константа в виде числа

  -- выражение с использованием полей участвуещих в группировке
  CONCAT('Отдел № ',DepartmentID) ConstAndGroupField, 
  CONCAT('Отдел № ',DepartmentID,', Должность № ',PositionID) ConstAndGroupFields,

  DepartmentID, -- поле из списка полей участвующих в группировке
  -- PositionID, -- поле учавствующее в группировке, не обязательно дублировать здесь

  COUNT(*) EmplCount, -- кол-во строк в каждой группе

  -- остальные поля можно использовать только с агрегатными функциями: COUNT, SUM, MIN, MAX, …
  SUM(Salary) SalaryAmount,
  MIN(ID) MinID
FROM Employees
GROUP BY DepartmentID,PositionID -- группировка по полям DepartmentID,PositionID

Так же стоит отметить, что группировку можно делать не только по полям, но также и по выражениям. Для примера сгруппируем данные по сотрудникам, по годам рождения:

SELECT
  CONCAT('Год рождения - ',YEAR(Birthday)) YearOfBirthday,
  COUNT(*) EmplCount
FROM Employees
GROUP BY YEAR(Birthday)

Рассмотрим пример с более сложным выражением. Для примера, получим градацию сотрудников по годам рождения:

SELECT
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END RangeName,
  COUNT(*) EmplCount
FROM Employees
GROUP BY
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END

SELECT
  ID,
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END
FROM Employees

Ну и конечно же вы можете объединять в блоке GROUP BY выражения с полями:

SELECT
  DepartmentID,
  CONCAT('Год рождения - ',YEAR(Birthday)) YearOfBirthday,
  COUNT(*) EmplCount
FROM Employees
GROUP BY YEAR(Birthday),DepartmentID -- порядок может не совпадать с порядком их использования в блоке SELECT
ORDER BY DepartmentID,YearOfBirthday -- напоследок мы можем применить к результату сортировку

Вернемся к нашей изначальной задаче. Как мы уже знаем, отчет очень понравился директору, и он попросил нас делать его еженедельно, дабы он мог мониторить изменения по компании. Чтобы, не перебивать каждый раз в Excel цифровое значение отдела на его наименование, воспользуемся знаниями, которые у нас уже есть, и усовершенствуем наш запрос:

SELECT
  CASE DepartmentID
    WHEN 1 THEN 'Администрация'
    WHEN 2 THEN 'Бухгалтерия'
    WHEN 3 THEN 'ИТ'
    ELSE 'Прочие'
  END Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees
GROUP BY DepartmentID
ORDER BY Info -- добавим для большего удобства сортировку по колонке Info

Но ничего, со временем, мы научимся делать все красиво, чтобы выборка у нас не зависела от появления в БД новых данных, а была динамической. Немного забегу вперед, чтобы показать к написанию каких запросов мы стремимся прийти:

SELECT
  ISNULL(dep.Name,'Прочие') DepName,
  COUNT(DISTINCT emp.PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(emp.Salary) SalaryAmount,
  AVG(emp.Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
GROUP BY emp.DepartmentID,dep.Name
ORDER BY DepName

В общем, не переживайте – все начинали с простого. Пока вам просто нужно понять суть конструкции GROUP BY.

Напоследок, давайте посмотрим каким образом можно строить сводные отчеты при помощи GROUP BY.

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

SELECT
  DepartmentID,
  SUM(CASE WHEN PositionID=1 THEN Salary END) [Бухгалтера],
  SUM(CASE WHEN PositionID=2 THEN Salary END) [Директора],
  SUM(CASE WHEN PositionID=3 THEN Salary END) [Программисты],
  SUM(CASE WHEN PositionID=4 THEN Salary END) [Старшие программисты],
  SUM(Salary) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

Можно конечно переписать и при помощи IIF:

SELECT
  DepartmentID,
  SUM(IIF(PositionID=1,Salary,NULL)) [Бухгалтера],
  SUM(IIF(PositionID=2,Salary,NULL)) [Директора],
  SUM(IIF(PositionID=3,Salary,NULL)) [Программисты],
  SUM(IIF(PositionID=4,Salary,NULL)) [Старшие программисты],
  SUM(Salary) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

Но в случае с IIF нам придется явно указывать NULL, которое возвращается в случае невыполнения условия.

В аналогичных случаях мне больше нравится использовать CASE без блока ELSE, чем лишний раз писать NULL. Но это конечно дело вкуса, о котором не спорят.

И давайте вспомним, что в агрегатных функциях при агрегации не учитываются NULL значения.

Для закрепления, сделайте самостоятельный анализ полученных данных по развернутому запросу:

SELECT
  DepartmentID,
  CASE WHEN PositionID=1 THEN Salary END [Бухгалтера],
  CASE WHEN PositionID=2 THEN Salary END [Директора],
  CASE WHEN PositionID=3 THEN Salary END [Программисты],
  CASE WHEN PositionID=4 THEN Salary END [Старшие программисты],
  Salary [Итого по отделу]
FROM Employees

И еще давайте вспомним, что если вместо NULL мы хотим увидеть нули, то мы можем обработать значение, возвращаемое агрегатной функцией. Например:

SELECT
  DepartmentID,
  ISNULL(SUM(IIF(PositionID=1,Salary,NULL)),0) [Бухгалтера],
  ISNULL(SUM(IIF(PositionID=2,Salary,NULL)),0) [Директора],
  ISNULL(SUM(IIF(PositionID=3,Salary,NULL)),0) [Программисты],
  ISNULL(SUM(IIF(PositionID=4,Salary,NULL)),0) [Старшие программисты],
  ISNULL(SUM(Salary),0) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

• вывести названия департаментов вместо их идентификаторов, например, добавив выражение CASE обрабатывающее DepartmentID в блоке SELECT
• добавьте сортировку по имени отдела при помощи ORDER BY

GROUP BY в скупе с агрегатными функциями, одно из основных средств, служащих для получения сводных данных из БД, ведь обычно данные в таком виде и используются, т.к. обычно от нас требуют предоставления сводных отчетов, а не детальных данных (простыней). И конечно же все это крутится вокруг знания базовой конструкции, т.к. прежде чем что-то подытожить (агрегировать), вам нужно первым делом это правильно выбрать, используя «SELECT … WHERE …».

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

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

Допустим, что вы дошли до этого момента

• Выбирать детальные данные по условию WHERE из одной таблицы
• Умеете пользоваться агрегатными функциями и группировкой из одной таблицы

Да, но они не учли, что вы пока не умеете строить запросы из нескольких таблиц, а только из одной, т.е. вы не умеете делать что-то вроде такого:

SELECT
  emp.*, -- вернуть все поля таблицы Employees
  dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
  pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID

И так, как же можно воспользоваться вашими текущими знаниями и получить при этом еще более продуктивные результаты?! Воспользуемся силой коллективного разума – идем к программистам, которые работают у вас, т.е. к Андрееву А.А., Петрову П.П. или Николаеву Н.Н., и попросим кого-нибудь из них написать для вас представление (VIEW или просто «Вьюха», так они даже, думаю, быстрее поймут вас), которое помимо основных полей из таблицы Employees, будет еще возвращать поля с «Названием отдела» и «Названием должности», которых вам так недостает сейчас для еженедельного отчета, которым вас загрузил Иванов И.И.

Т.к. вы все грамотно объяснили, то ИТ-шники, сразу же поняли, что от них хотят и создали, специально для вас, представление с названием ViewEmployeesInfo.

Представляем, что вы следующей команды не видите, т.к. это делают ИТ-шники:

CREATE VIEW ViewEmployeesInfo
AS
SELECT
  emp.*, -- вернуть все поля таблицы Employees
  dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
  pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID

Т.е. для вас весь этот, пока страшный и непонятный, текст остается за кадром, а ИТ-шники дают вам только название представления «ViewEmployeesInfo», которое возвращает все вышеуказанные данные (т.е. то что вы у них просили).

Вы теперь можете работать с данным представлением, как с обычной таблицей:

SELECT *
FROM ViewEmployeesInfo

SELECT
  DepartmentName,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg
FROM ViewEmployeesInfo emp
GROUP BY DepartmentID,DepartmentName
ORDER BY DepartmentName

Т.е. для вас в данном случае, будто бы ничего и не поменялось, вы продолжаете так же работать с одной таблицей (только уже правильнее сказать с представлением ViewEmployeesInfo), которое возвращает все необходимые вам данные. Благодаря помощи ИТ-шников, детали по добыванию DepartmentName и PositionName остались для вас в черном ящике. Т.е. представление для вас выглядит так же, как и обычная таблица, считайте, что это расширенная версия таблицы Employees.

Давайте для примера еще сформируем ведомость, чтобы вы убедились, что все действительно так как я и говорил (что вся выборка идет из одного представления):

SELECT
  ID,
  Name,
  Salary
FROM ViewEmployeesInfo
WHERE Salary IS NOT NULL
  AND Salary>0
ORDER BY Name

Использование представлений в некоторых случаях, дает возможность значительно расширить границы пользователей, владеющих написанием базовых SELECT-запросов. В данном случае представление, представляет собой плоскую таблицу со всеми необходимыми пользователю данными (для тех, кто разбирается в OLAP, это можно сравнить с приближенным подобием OLAP-куба с фактами и измерениями).

Вырезка с википедии. Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов он стал настолько сложным, что превратился в инструмент программиста.

Как видите, уважаемые пользователи, язык SQL изначально задумывался, как инструмент для вас. Так что, все в ваших руках и желании, не отпускайте руки.

HAVING – наложение условия выборки к сгруппированным данным

Рассмотрим пример:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000

Т.е. здесь в первую очередь происходит группировка и вычисляются данные по всем отделам:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. получаем сгруппированные данные по всем отделам

А уже к этим данным применяется условие указанно в блоке HAVING:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. получаем сгруппированные данные по всем отделам
HAVING SUM(Salary)>3000 -- 2. условие для фильтрации сгруппированных данных

В HAVING-условии так же можно строить сложные условия используя операторы AND, OR и NOT:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000 AND COUNT(*)<2 -- и число людей меньше 2-х

Как можно здесь заметить агрегатная функция (см. «COUNT(*)») может быть указана только в блоке HAVING.

Соответственно мы можем отобразить только номер отдела, подпадающего под HAVING-условие:

SELECT
  DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000 AND COUNT(*)<2 -- и число людей меньше 2-х

Пример использования HAVING-условия по полю включенного в GROUP BY:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. сделать группировку
HAVING DepartmentID=3 -- 2. наложить фильтр на результат группировки

Это только пример, т.к. в данном случае проверку логичнее было бы сделать через WHERE-условие:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- 1. провести фильтрацию детальных данных
GROUP BY DepartmentID -- 2. сделать группировку только по отобранным записям

Т.е. сначала отфильтровать сотрудников по отделу 3, и только потом сделать расчет.

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

Думаю, на этом рассказ о HAVING-условиях можно окончить.

Подведем итоги

Конечно же, вы так же можете применить к сгруппированным данным предложения DISTINCT и TOP, изученные во второй части.

Эти предложения в данном случае применятся к окончательному результату:

SELECT
  TOP 1 -- 6. применится в последнюю очередь
    SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000
ORDER BY DepartmentID -- 5. сортировка результата

SELECT
  DISTINCT -- показать только уникальные значения SalaryAmount
    SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID

Как получились данные результаты проанализируйте самостоятельно.

Заключение

Если базовая конструкция позволяла нам получить необходимые детальные данные, то применение агрегатных функций и группировок к этим детальным данным, дало нам возможность получить по ним сводные данные. Так что, как видите здесь все важно, т.к. одно опирается на другое – без знания базовой конструкции мы не сможем, например, правильно отобрать данные, по которым нам нужно просчитать итоги.

Здесь я намеренно стараюсь показывать только основы, чтобы сосредоточить внимание начинающих на самых главных конструкциях и не перегружать их лишней информацией. Твердое понимание основных конструкций (о которых я еще продолжу рассказ в последующих частях) даст вам возможность решить практически любую задачу по выборке данных из РБД. Основные конструкции оператора SELECT применимы в таком же виде практически во всех СУБД (отличия в основном состоят в деталях, например, в реализации функций – для работы со строками, временем, и т.д.).

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

• GROUP BY ROLLUP(…), GROUP BY GROUPING SETS(…), …
• PIVOT, UNPIVOT
• и т.п.

Если вы делаете первые шаги в SQL, то сосредоточьтесь в первую очередь, именно на изучении базовых конструкций, т.к. владея базой, все остальное вам понять будет гораздо легче, и к тому же самостоятельно. Вам в первую очередь, как бы нужно объемно понять возможности языка SQL, т.е. какого рода операции он вообще позволяет совершить над данными. Донести до начинающих информацию в объемном виде – это еще одна из причин, почему я буду показывать только самые главные (железные) конструкции.

Удачи вам в изучении и понимании языка SQL.

Часть четвертая — habrahabr.ru/post/256045

CASE ОПЕРАТОР — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать оператор CASE в Oracle/PLSQL c синтаксисом и примерами.

Описание

В Oracle/PLSQL оператор CASE имеет функциональность IF-THEN-ELSE. Начиная с Oracle 9i, вы можете использовать оператор CASE в SQL предложении.

Синтаксис

WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n

ELSE result

END

Параметры или аргументы

expression не является обязательным. Это значение, которое вы сравниваете с условиями (то есть: condition_1, condition_2 … condition_n).

condition_1 .. condition_n должны быть одного типа. Условия оцениваются по порядку, одно за другим. После того, как условие примет значение TRUE (истина), оператор CASE вернет результат, и не будет оценивать условия дальше.

result_1 .. result_n все должны быть одного типа данных. Это значение возвращается единожды, когда условие примет TRUE (истина).

Примечание

• Если условие не примет TRUE, то оператор CASE вернет значение предложения ELSE.
• Если предложение ELSE опущено и условие не примет TRUE, то оператор CASE вернет NULL.
• Оператор CASE может иметь до 255 сравнений. Каждое предложение WHEN … THEN рассматривает 2 сравнения.

Применение

Оператор CASE можно использовать в следующих версиях Oracle / PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Пример

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

SELECT table_name, CASE owner WHEN ‘SYS’ THEN ‘The owner is SYS’ WHEN ‘SYSTEM’ THEN ‘The owner is SYSTEM’ ELSE ‘The owner is another value’ END FROM all_tables;

Или вы могли бы написать SQL запрос, используя оператор CASE следующим образом: (опустив expression в предложение WHEN .. THEN). Например:

SELECT table_name, CASE WHEN owner=’SYS’ THEN ‘The owner is SYS’ WHEN owner=’SYSTEM’ THEN ‘The owner is SYSTEM’ ELSE ‘The owner is another value’ END FROM all_tables;

Эти два примера оператора CASE эквивалентны следующему условному оператору IF-THEN-ELSE:

IF owner = ‘SYS’ THEN result := ‘The owner is SYS’; ELSIF owner = ‘SYSTEM’ THEN result := ‘The owner is SYSTEM»; ELSE result := ‘The owner is another value’; END IF;

Оператор CASE будет сравнивать каждое значение owner, одно за другим.

Предложение ELSE в операторе CASE не является обязательным. Его можно опустить. Давайте посмотрим предыдущий SQL запрос с опущенным предложением ELSE.

SQL запрос будет выглядеть следующим образом:

SELECT table_name, CASE owner WHEN ‘SYS’ THEN ‘The owner is SYS’ WHEN ‘SYSTEM’ THEN ‘The owner is SYSTEM’ END FROM all_tables;

С отсутствующим предложением ELSE, если ни одно из условий не приняло TRUE, оператор CASE вернет NULL.

Сравнивая 2 условия

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

SELECT CASE WHEN a < b THEN ‘hello’ WHEN d < e THEN ‘goodbye’ END FROM suppliers;

Сравнение 2-х полей в одном предложении CASE:

SELECT supplier_id, CASE WHEN supplier_name = ‘IBM’ and supplier_type = ‘Hardware’ THEN ‘North office’ WHEN supplier_name = ‘IBM’ and supplier_type = ‘Software’ THEN ‘South office’ END FROM suppliers;

Итак, если поле supplier_name = ‘IBM’ и поле supplier_type = ‘Hardware’, то оператор CASE вернет ‘North office’. Если поле supplier_name = ‘IBM’ и supplier_type = ‘Software’, оператор CASE вернет ‘South office’.

MySQL. Использование IF и CASE в SELECT.

Есть ситуации, когда в зависимости от определенных условий в блоке SELECT в запросе нужно возвращать определенные поля или же под один алиас возвращать разные значения. Как это сделать и в каких ситуациях это решает проблему я покажу в этой статье.

Ситуация из реальной практики — в БД хранится цена и рядом с ней хранится валюта этой цены. Т.е. у одной записи может быть цена 5 000 и валюта UAH, а во второй — 500, но с валютой USD. Соответственно чтобы сделать, например, сортировку, следует как-то унифицировать цену.

Для этого нужно привести обе цены к одной — например, к USD. Т.е. задача такая — если поле имеет валюту UAH — селектим просто цену, а в случае, если валюта — USD — цену домножаем на курс.

Вариант решения при помощи CASE:

SELECT case price_currency when «UAH» then price when «USD» then ( 25 * price) end as one_price . . . . . . .

Вариант решения при помощи IF:

SELECT IF(price_currency = ‘UAH’, price, 25 * price) as one_price . . . . . . .

Оба варианта вернут для каждой записи значение, соотв. цене в UAH.

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

В таком случае лучше использовать CASE.

Вот и все. Если остались вопросы — пишите в комментариях. Удачи!

Похожие записи

Interbase. Как в select запрос вставить выбор (if или case)?

Select if F1>100 then "больше" else "меньше" from table1

Подскажите синтаксис. Не могу найти описания в описании (language reference).

Приведенный пример выдает ошибку, что не знает слова if.

В описании FB есть это.
Только в ReleaseNotes читать надо.

Оригинальная документация:
=====================================================================
SQL Language Extension: CASE

Function:
   Allow the result of a column to be determined by a the results of a
   case expression.

Author:
   Arno Brinkman <[email protected]>

Format:
 <case expression> ::=
     <case abbreviation>
   | <case specification>

 <case abbreviation> ::=
     NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
   | COALESCE <left paren> <value expression> { <comma> <value expression> }… <right paren>

 <case specification> ::=
     <simple case>
   | <searched case>

 <simple case> ::=
   CASE <value expression>
     <simple when clause>…
     [ <else clause> ]
   END

 <searched case> ::=
   CASE
     <searched when clause>…
     [ <else clause> ]
   END

 <simple when clause> ::= WHEN <when operand> THEN <result>

 <searched when clause> ::= WHEN <search condition> THEN <result>

 <when operand> ::= <value expression>

 <else clause> ::= ELSE <result>

 <result> ::=
     <result expression>
   | NULL

 <result expression> ::= <value expression>

Notes:
   See also README.data_type_results_of_aggregations.txt

Examples:

A) (simple)
 SELECT
   o.ID,
   o.Description,
   CASE o.Status
     WHEN 1 THEN «confirmed»
     WHEN 2 THEN «in production»
     WHEN 3 THEN «ready»
     WHEN 4 THEN «shipped»
     ELSE «unknown status «»» || o.Status || «»»»
   END
 FROM
   Orders o

B) (searched)
 SELECT
   o.ID,
   o.Description,
   CASE
     WHEN (o.Status IS NULL) THEN «new»
     WHEN (o.Status = 1) THEN «confirmed»
     WHEN (o.Status = 3) THEN «in production»
     WHEN (o.Status = 4) THEN «ready»
     WHEN (o.Status = 5) THEN «shipped»
     ELSE «unknown status «»» || o.Status || «»»»
   END
 FROM
   Orders o

Сорри, предыдущий пост оффтоп. У тебя Интербаза, на ней наверное не прокатит.
Все выше перечисленное начал использовать с версии Firebird v1.5.3.4870.

Использовать ХП

Оператор SELECT на шаге

Каждый начинающий программист SAS изучает простой оператор IF-THEN / ELSE для условной обработки на этапе SAS DATA. Базовый оператор If-THEN обрабатывает два случая: если условие истинно, программа выполняет одно действие, в противном случае программа выполняет другое действие.

Конечно, вы можете обрабатывать больше случаев, используя несколько операторов ELSE IF. Я видел программы SAS, которые содержат десятки предложений ELSE.Иногда необходима длинная последовательность операторов IF-THEN / ELSE, например, когда вы проверяете сложные логические условия.

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

В следующем примере используется файл Sashelp.Набор данных сердца, который содержит данные для 5167 пациентов в медицинском исследовании. Переменная Smoking_Status — это категориальная переменная, которая кодирует среднее количество сигарет, выкуриваемых каждым пациентом в день. В следующем шаге просмотра DATA реализована схема перекодирования, которая иногда является самым простым способом заставляет уровни номинальной переменной появляться в естественном порядке во время анализа SAS.

 / * пример использования оператора SELECT * /
данные Heart / view = Heart;
установить sashelp.сердце;
выберите (Smoking_Status);
   when ('Некурящий') Smoking_Cat = 1;
   when ('Light (1-5)') Smoking_Cat = 2;
   when ('Умеренный (6-15)') Smoking_Cat = 3;
   when ('Heavy (16-25)') Smoking_Cat = 4;
   when ('Очень тяжелый (> 25)') Smoking_Cat = 5;
   в противном случае Smoking_Cat = .;
конец;
бежать; 

Оператор SELECT-WHEN легко читается. Вы указываете имя переменной в операторе SELECT. Затем вы перечисляете последовательность операторов WHEN.Каждый оператор WHEN указывает конкретное значение для переменной. Если переменная имеет это значение, программа условно выполняет оператор, который в этом примере присваивает значение переменной Smoking_Cat.

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

Вы также можете комбинировать категории в операторе WHEN. Например, при статистическом анализе вы можете захотеть объединить категории «тяжелые» и «очень тяжелые» в одну группу.В операторе WHEN вы можете указать несколько значений в списке, разделенном запятыми:

 / * объединяет категории «тяжелые» и «очень тяжелые» * /
   when ('Heavy (16-25)', 'Very Heavy (> 25)') Smoking_Cat = 4; 

Если условие WHEN истинно, программа выполнит один оператор. Это то же правило, которому следует оператор IF-THEN. Чтобы выполнить более одного оператора, используйте блок DO-END, который группирует операторы вместе:

 когда («Некурящий») курят; / * выполнение нескольких операторов * /
      Smoking_Cat = 1;
      IsSmoker = 0;
   конец; 

Я использую оператор SELECT-WHEN в качестве «поиска по таблице», когда программе необходимо выполнить ветвление в соответствии со значением одной категориальной переменной, имеющей три или более допустимых значения.Базовый Оператор SELECT-WHEN не так гибок, как оператор IF-THEN / ELSE, но, когда это применимо, дает очень чистые и удобные для чтения программы.

В других языках есть аналогичные операторы ветвления. Язык SQL поддерживает оператор CASE-WHEN. Языки C / C ++ и Java / Javascript поддерживают оператор switch-case. В то время как оператор CASE-WHEN в SAS выполняет один оператор, оператор switch-case реализует падение через , поэтому программисты на C часто используют разрыв оператор для выхода из блока переключения.

Некоторые языки не поддерживают специальный оператор switch, но вместо этого требуют, чтобы вы использовали операторы IF-THEN / ELSE. В эту категорию попадают Python и язык SAS / IML.

Существует альтернативный синтаксис для оператора SELECT-WHEN, который не определяет выражение в операторе SELECT. Вместо этого вы указываете логические условия в операторах WHEN. Этот альтернативный синтаксис по сути эквивалентен оператору IF-THEN / ELSE, поэтому какой синтаксис вы используете — вопрос личных предпочтений.Лично я использую SELECT-WHEN для перехода к известному набору дискретных значений, а оператор IF-THEN / ELSE — для обработки более сложных ситуаций.

PostgreSQL CASE

Резюме : в этом руководстве вы узнаете, как использовать условное выражение PostgreSQL CASE для формирования условных запросов.

Выражение PostgreSQL CASE совпадает с оператором IF / ELSE в других языках программирования.Это позволяет вам добавить в запрос логику if-else, чтобы сформировать мощный запрос.

Поскольку CASE является выражением, вы можете использовать его в любых местах, где может использоваться выражение, например, SELECT , WHERE , GROUP BY и HAVING clause.

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

1) Общее выражение PostgreSQL CASE

Ниже показана общая форма оператора CASE :

 
  CASE
      КОГДА условие_1 ТО результат_1
      КОГДА условие_2 ТО результат_2
      [КОГДА ...]
      [ELSE else_result]
END  
 

В этом синтаксисе каждое условие ( условие_1 , условие_2 …) является логическим выражением, которое возвращает либо истина, , либо ложь .

Когда условие оценивается как false , выражение CASE оценивает следующее условие сверху вниз, пока не найдет условие, которое оценивается как true .

Если условие оценивается как истинно , выражение CASE возвращает соответствующий результат, следующий за условием.Например, если condition_2 оценивается как true , выражение CASE возвращает result_2 . Кроме того, он немедленно прекращает вычисление следующего выражения.

Если все условия оцениваются как false , выражение CASE возвращает результат ( else_result ), следующий за ключевым словом ELSE . Если вы опустите предложение ELSE , выражение CASE вернет NULL .

Давайте взглянем на таблицу film из образца базы данных.

A) Общий пример CASE

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

• Если продолжительность меньше 50 минут, фильм короткий.
• Если продолжительность больше 50 минут и меньше или равна 120 минутам, фильм средний.
• Если продолжительность превышает 120 минут, фильм получается длинным.

Чтобы применить эту логику, можно использовать выражение CASE в операторе SELECT следующим образом:

 
  заголовок SELECT,
       длина,
       ДЕЛО
           КОГДА длина> 0
                И длина <= 50 ТОГДА 'Короткая'
           КОГДА длина> 50
                И длина <= 120 ТОГДА "Средний"
           КОГДА длина> 120 ТОГДА 'Длинная'
       КОНЕЦ продолжительность
ИЗ фильма
ЗАКАЗАТЬ ПО названию;  
 

Вывод:

Обратите внимание, что мы поместили псевдоним столбца duration после выражения CASE .

B) Использование CASE в примере агрегатной функции

Предположим, что вы хотите назначить ценовые сегменты фильмам со следующей логикой:

• Если арендная ставка 0,99, фильм экономичен.
• Если ставка проката 1,99, фильм массовый.
• Если ставка проката 4,99 — фильм премиум-класса.

И вы хотите узнать количество фильмов, относящихся к экономичным, массовым и премиальным.

В этом случае вы можете использовать выражение CASE для построения запроса следующим образом:

 
  SELECT
СУММА (CASE
               КОГДА rent_rate = 0.99 ТО 1
Иначе 0
КОНЕЦ
) AS "Экономика",
СУММ (
ДЕЛО
КОГДА арендная ставка = 2,99 ТО 1
Иначе 0
КОНЕЦ
) АС "Масса",
СУММ (
ДЕЛО
КОГДА арендная ставка = 4,99 ТО 1
Иначе 0
КОНЕЦ
) AS "Premium"
ИЗ
фильм;  
 

Результат запроса следующий:

В этом примере мы использовали выражение CASE , чтобы вернуть 1 или 0, если арендная ставка попадает в каждый ценовой сегмент. И мы применили функцию СУММ , чтобы подсчитать общее количество фильмов для каждого ценового сегмента.

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

PostgreSQL предоставляет другую форму выражения CASE , называемую простой формой, как показано ниже:

 
  выражение CASE
   КОГДА значение_1 ТО результат_1
   КОГДА значение_2 ТО результат_2
   [КОГДА ...]
ELSE
   else_result
END  
 

CASE сначала оценивает выражение и сравнивает результат с каждым значением ( значение_1 , значение_2 ,…) в предложениях WHEN последовательно, пока не будет найдено совпадение.

Если результат выражения равен значению (значение1, значение2 и т. Д.) В предложении WHEN , CASE возвращает соответствующий результат в предложении THEN .

Если CASE не находит совпадений, он возвращает else_result , после которого следует ELSE или NULL значение, если ELSE недоступно.

A) Простой пример выражения PostgreSQL CASE

Следующий оператор использует выражение CASE для добавления описания рейтинга к выходным данным:

 
  SELECT title,
       рейтинг,
       Рейтинг CASE
           КОГДА 'G' ТОГДА 'для широкой аудитории'
           КОГДА "PG" ТОГДА "Рекомендуется родительский контроль"
           КОГДА "PG-13" ТОГДА "Родители строго осторожны"
           КОГДА 'R' ТОГДА 'Запрещено'
           КОГДА "NC-17" ТОЛЬКО "Только для взрослых"
       END rating_description
ИЗ фильма
ЗАКАЗАТЬ ПО названию;  
 

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

B) Использование простого выражения PostgreSQL CASE с примером агрегатной функции

Следующий оператор использует выражение CASE с функцией SUM для вычисления количества фильмов в каждом рейтинге:

 
  SELECT
       СУММ (рейтинг CASE
             КОГДА 'G' ТО 1
Иначе 0
КОНЕЦ) "Для широкой публики",
       СУММ (рейтинг CASE
             КОГДА 'PG' ТО 1
Иначе 0
КОНЕЦ) "Рекомендуется родительский контроль",
       СУММ (рейтинг CASE
             КОГДА 'PG-13' ТО 1
Иначе 0
КОНЕЦ) "Настоятельно предостерегаем родителей",
       СУММ (рейтинг CASE
             КОГДА 'R' ТОГДА 1
Иначе 0
КОНЕЦ) "Запрещено",
       СУММ (рейтинг CASE
             КОГДА 'NC-17' ТО 1
Иначе 0
КОНЕЦ) «Только для взрослых»
ИЗ пленки;  
 

В этом руководстве вы узнали, как использовать выражение PostgreSQL CASE для формирования сложных запросов.

• Было ли это руководство полезным?
• Да Нет

СЛУЧАЙ, КОГДА выписка

Универсальный вариант CASE WHEN … END ответ на многие вопросы по SQL!

искомый синтаксис CASE

Базовый синтаксис этого типа оператора CASE выглядит следующим образом:

КОРПУС

КОГДА условие 1 выполняется ТОГДА результат 1

КОГДА условие 2 выполняется ТОГДА результат 2

…

ELSE результат №

END AS ‘Псевдоним столбца’

Предложение ELSE является необязательным. Что касается Excel Если функция , обработка прекращается, как только условие выполняется.

Пример искомого оператора CASE — возрастные группы

Предположим, вы хотите разделить актеров на старых, средних и молодых, как Рождества 2013 г. (некоторые люди могут не согласиться с категоризацией в этот пример!). Вот как это сделать:

ВЫБРАТЬ

ActorName,

CONVERT (символ (10), ActorDob, 103) AS ‘Когда родился’,

КОРПУС

КОГДА DateDiff (год, ActorDob, ‘20131225’) ТОГДА ‘Молодой’

КОГДА DateDiff (год, ActorDob, ‘20131225’) ТОГДА «Среднего возраста»

ELSE ‘Старый’

END AS ‘Возрастная категория’

ИЗ

табл Актер

Вот некоторые из субъектов, которые может вернуть этот запрос:

Кирстен Данст пищит в нашу Молодые категории!

Простые операторы CASE

Синтаксис этого типа оператора CASE очень похож:

CASE-выражение

КОГДА значение1 ТО результат 1

КОГДА значение2 ТО исход 2

…

ELSE результат №

КОНЕЦ

Разница в том, что вы помещаете то, что оцениваете, после CASE ключевое слово. Хотя это выглядит проще, это на самом деле менее полезен (например, его нельзя использовать в приведенном выше примере, как сравнения).

Вот пример оператора SQL для отображения количества Оскаров, выигранных фильмами. как текст:

ВЫБРАТЬ

FilmName,

ФильмOscarWins,

КЕЙС Пленка OscarWins

КОГДА 0 ТО «Не победитель»

КОГДА 1 ТО «Оскар»

КОГДА 2 ТО «Двойной»

ELSE ‘Лоты’

КОНЕЦ КАК Оскар

ИЗ

тблФленма

Это даст следующий результат:

Количество Оскаров также отображается в виде текста.

Вложенные операторы CASE

Вы можете вложить операторы CASE друг в друга, хотя очень важно, чтобы вы правильно делали отступ в своем SQL, чтобы результат был понятным:

ВЫБРАТЬ

ActorName,

ActorGender,

CONVERT (символ (10), ActorDob, 103) AS Dob,

CASE ActorGender

КОГДА «Мужской» ТО

КОРПУС

КОГДА Год (ActorDob) ТОГДА ‘Человек’

ELSE ‘Мальчик’

КОНЕЦ

КОГДА ‘Женский’ ТО

КОРПУС

КОГДА Год (ActorDob) ТО ‘Женщина’

ELSE ‘Девушка’

КОНЕЦ

ELSE ‘Другое’

END AS Категория

ИЗ

табл Актер

Этот запрос разделит актеров на мужчин, женщин, мальчиков (!) И девочек. (!):

Результаты выполнения указанного выше запроса (у нас нет видимых молодых мужчин).

Использование операторов CASE в предложениях GROUP BY

Предположим, вы хотите сгруппировать фильмы в следующие диапазоны длины:

Вот как должен выглядеть ответ:

Количество короткометражных, средних и длинных фильмов.

Чтобы это заработало, вы должны сделать это:

ВЫБРАТЬ

КОРПУС

КОГДА FilmRunTimeMinutes THEN ‘Short’

КОГДА FilmRunTimeMinutes THEN ‘Medium’

ELSE ‘Длинный’

Категория END AS,

COUNT (*) AS ‘Количество фильмов’

ИЗ

тблФленма

ГРУППА ПО

Категория

Проблема в том, что нельзя сгруппировать по столбцу Категория , потому что это вычисляемый столбец (и вы можете использовать их только в ORDER BY инструкции SQL, а не WHERE или GROUP BY пунктов).

Я могу придумать 3 способа решить эту проблему. Первый (и наиболее очевидный) — просто повторить оператор CASE :

ВЫБРАТЬ

КОРПУС

КОГДА FilmRunTimeMinutes THEN ‘Short’

КОГДА FilmRunTimeMinutes THEN ‘Medium’

ELSE ‘Длинный’

Категория END AS,

COUNT (*) AS ‘Количество фильмов’

ИЗ

тблФленма

ГРУППА ПО

КОРПУС

КОГДА FilmRunTimeMinutes THEN ‘Short’

КОГДА FilmRunTimeMinutes THEN ‘Medium’

ELSE ‘Длинный’

КОНЕЦ

Очевидные недостатки этого:

1. Вы можете ошибиться во второй раз и будете группировка и отображение двух разных выражений.
2. Очень много печатать!
3. Это делает итоговый SQL-запрос трудным для чтения.

Остальные два метода следующие:

Пример SQL для этого второго метода выглядит следующим образом:

СОЗДАТЬ ПРОСМОТР vwFilmRunTimes AS

ВЫБРАТЬ

КОРПУС

КОГДА FilmRunTimeMinutes THEN ‘Short’

КОГДА FilmRunTimeMinutes THEN ‘Medium’

ELSE ‘Длинный’

Категория END AS,

Название фильма

ИЗ

тблФленма

GO

ВЫБРАТЬ

,

COUNT (*) AS ‘Количество фильмов’

ИЗ

vwFilmRunTimes

ГРУППА ПО

Категория

Использование операторов CASE для управления логикой программы

Вы не можете этого сделать, но иногда есть способ обойти это.Объяснять что я имею в виду, рассмотрим этот пример запроса, чтобы распечатать сообщение по количеству короткометражных фильмов в таблице:

ЕСЛИ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ tblFilm ГДЕ FilmRunTimeMinutes

ПЕЧАТЬ ‘Есть действительно короткие фильмы’

ELSE

ЕСЛИ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ tblFilm ГДЕ FilmRunTimeMinutes

ПЕЧАТЬ ‘Есть короткометражные фильмы’

ELSE

ПЕЧАТЬ ‘Короткометражных фильмов не найдено’

Здесь используются подзапросы, но, надеюсь, достаточно ясно, что происходит.Это вложенное условие IF трудно прочитать, и будет сложнее, чем больше возможных условий. Было бы намного легче читать и напишите, используя CASE , но следующее не сработает:

КОРПУС

КОГДА СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ tblFilm ГДЕ FilmRunTimeMinutes

ТОГДА ПЕЧАТЬ «Есть действительно короткометражки»

КОГДА СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ tblFilm ГДЕ FilmRunTimeMinutes

ТОГДА ПЕЧАТЬ ‘Есть короткометражные фильмы’

ELSE

ПЕЧАТЬ ‘Короткометражных фильмов не найдено’

КОНЕЦ

Это потому, что вы не можете использовать CASE для управления потоком программы. Однако вы можете использовать его для вычисления выражений, что приведет к следующему рабочий:

ЗАЯВИТЬ @Message varchar (MAX)

НАБОР @Message =

КОРПУС

КОГДА СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ tblFilm ГДЕ FilmRunTimeMinutes

ЗАТЕМ ‘Есть действительно короткие фильмы’

КОГДА СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ tblFilm ГДЕ FilmRunTimeMinutes

ТО ‘Есть короткометражные фильмы’

ELSE

‘Короткометражных фильмов не найдено’

КОНЕЦ

ПЕЧАТЬ @ Сообщение

Здесь используются переменные, но, надеюсь, будет достаточно очевидно, что запрос делает!