ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ ВЫРАЖЕНИЯ CASE

Скалярные выражения

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

create table   T1 (a int, b int,   c int)
select
    case
          when T1.a > 0   then
              T1.b
          else
              T1.c
    end
from T1

Этот план запроса подразумевает просмотр таблицы T1 и оценку выражения CASE для каждой её строки. Оператор Compute Scalar вычисляет значение выражения CASE, включая оценку условия и принятие решения, будет ли выполняться оценка в предложении THEN или ELSE. Если в выражение CASE поместить подзапросы, всё становится немного сложнее и существенно интересней.

Предложение WHEN

Давайте сначала добавим к предложению WHEN простой подзапрос:

create table   T2 (a int, b int)
select
    case
          when exists   (select * from   T2 where T2.a = T1.a) then
              T1.b
          else
              T1.c
    end
from T1

Предложение THEN

Давайте теперь попробуем добавить к предложению THEN простой подзапрос:

create table   T3 (a int unique   clustered, b int)
insert T1 values(0, 0,   0)
insert T1 values(1, 1,   1)

select
    case
          when T1.a > 0   then
              (select T3.b from   T3 where T3.a = T1.b)
          else
              T1.c
    end
from T1

Я добавил к T3 ограничение уникальности, позволяющее гарантировать, что скалярный подзапрос возвратит только одну строку. Без ограничения, план запроса был бы более сложен, поскольку оптимизатору нужно было бы гарантировать, что подзапрос действительно возвратит только одну строку, и ему пришлось бы выдавать ошибку, если бы вернулось больше одной строки.
Я также добавил в T1 ещё две строки, причём, условие в предложение WHEN выдаст ложь для первой строки и истину для второй строки. Таким образом, первая строка у нас будет подходить для ELSE, а вторая для THEN. Обратите внимание, что значение подзапроса в THEN будет использоваться, только если предложение WHEN будет истинно.
Ниже показан профиль статистики для плана исполнения этого запроса:

Этот план запроса использует специальный тип соединения вложенных циклов, в котором задействуется предикат PASSTHRU. Соединение оценивает предикат PASSTHRU для каждой внешней строки. Если предикат PASSTHRU оценивается как истина, соединение немедленно возвращает строку, подобную полусоединению или внешнему соединению. Если же предикат PASSTHRU оценивается как ложь, соединение выполняется обычным образом, т.е. выполняется попытка соединения внешней строки с внутренней строкой.
В показанном выше примере, предикат PASSTHRU выражения CASE является инверсией (обратите внимание на функцию IsFalseOrNull) предложения WHEN. Если предложение WHEN оценивается как истина, предикат PASSTHRU оценивается как ложь, происходит соединение, и поиск по внутренней части соединения выполняет оценку подзапроса THEN. Если предложение WHEN оценивается как ложь, предикаты PASSTHRU оценивается как истина, соединение пропускается, а поиск или подзапрос THEN не выполняется.
Обратите внимание, что просмотр T1 возвращает 2 строки, хотя поиск в T3 выполняется только один раз. Так происходит потому, что в нашем примере предложение WHEN истинно только для одной из двух строк. Предиката PASSTHRU является единственным механизмом, когда число строк на внешней стороне соединения вложенных циклов не соответствует в точности числу строк на внутренней стороне.
Также обратите внимание, что после того, как будет использовано внешнее соединение, невозможно гарантировать, что подзапрос в THEN вернёт хоть что-нибудь (в действительности гарантируется только то, что благодаря ограничению уникальности будет возвращено не более одной строки). Если подзапрос не возвращает строк, внешнее соединение просто возвратит NULL для T3.b. Если бы использовалось внутреннее соединение, отказаться от строки T1 было бы неправильно. Предостережение: я прогонял эти примеры на SQL Server 2005. Если Вы будете выполнять этот пример на SQL Server 2000, предикат PASSTHRU будет виден, но в плане исполнения запроса он появится как регулярный предикат предложения WHERE. К сожалению, для SQL Server 2000 не существует простого пути различения регулярных предикатов и предиката PASSTHRU.

Предложение ELSE и несколько предложений WHEN

Подзапрос в предложении ELSE работает точно так же, как и подзапрос в предложении THEN. Для оценки условия подзапроса будет использован предикат PASSTHRU.

Точно так же выражение CASE с несколькими предложениями WHEN с подзапросами в каждом предложении THEN будет работать аналогичным образом. Отличие только в том, что предикатов PASSTHRU будет больше.
Например:

create table   T4 (a int unique   clustered, b int)
create table   T5 (a int unique   clustered, b int)

select
    case
          when T1.a > 0   then
              (select T3.b from   T3 where T3.a = T1.a)
          when T1.b > 0   then
              (select T4.b from   T4 where T4.a = T1.b)
          else
              (select T5.b from T5   where T5.a = T1.c)
    end
from T1

                                              ORDERED FORWARD)

Столбец пробной таблицы в качестве предиката PASSTHRU

Наконец, давайте рассмотрим запрос с подзапросами в предложениях WHEN и в предложениях THEN. Также, для разнообразия, давайте переместим выражение CASE из списка SELECT в предложение WHERE.

select *
from T1
where 0   =
    case
          when exists   (select * from   T2 where T2.a = T1.a) then
              (select T3.b from   T3 where T3.a = T1.b)
          else
              T1.c
    end

В этом плане исполнения запроса имеется левое полусоединение со столбцом пробной таблицы, позволяющее оценить подзапрос в предложении WHEN, и соединение вложенных циклов с предикатом PASSTHRU для столбца пробной таблицы, позволяющее решить, выполнять ли оценку подзапроса в предложении THEN. Поскольку выражение CASE было перемещено в предложение WHERE, для оценки выходных значений из списка SELECT вместо оператора Compute Scalar используется оператор Filter, с помощью которого определяется, какие строки будут возвращены. Все остальное работает точно так же.

Далее…

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

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

Что такое условная логика?

Под условной логикой понимается наличие у программы нескольких путей выполнения в зависимости от каких-то условий.

Например, в базе данных «Расписание» есть таблица с полем , отражающим дату рождения студента. Допустим,
в выборке необходимо отобразить не саму дату рождения, а текстовое значение «Совершеннолетний» или «Несовершеннолетний» в зависимости от того, есть ли студенту 18 лет. Это и есть пример условной логики, при которой должно вывестись либо одно значение, либо другое в зависимости от конкретного условия.

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

 first_name last_name

   TIMESTAMPDIFF birthday     
   
  
 Student

Синтаксис поискового выражения CASE


     условие_1  возвращаемое_значение_1
     условие_2  возвращаемое_значение_2
     условие_n  возвращаемое_значение_n
     возвращаемое_значение_по_умолчанию

Если возвращает истинное значение, то выражение вернёт , иначе будет сделана проверка на и т.д. Если ни одно из предложенный условий не будет выполнено, то вернётся или , если была использована конструкция .

Пример

Рассмотрим оператор на примере определения этапа школьного образования.

Этапы школьного образования

 name

   SUBSTRINGname  INSTRname      
   SUBSTRINGname  INSTRname         
   
  stage
 Class
  • Сначала мы извлекаем номер класса из его названия
    SUBSTRINGname  INSTRname 
    
  • Далее мы проверяем вхождение данного номера в список классов, относящихся к «Старшая школа» и «Средняя школа».
  • Если номер класса не находится в диапазоне 5–11, мы выводим «Начальная школа».

Синтаксис простого выражения CASE

Оператор также имеет и более простой синтаксис, который схож с поисковым выражением , но
является менее гибким. Так он выглядит в общем виде:

 значение
     сравниваемое_значение_1  возвращаемое_значение_1
     сравниваемое_значение_2  возвращаемое_значение_2
     сравниваемое_значение_n  возвращаемое_значение_n
     возвращаемое_значение_поумолчанию
 name
 SUBSTRINGname  INSTRname 
     
     
     
     
     
     
     
   
  stage
 Class

Выражение 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

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

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

Здесь делается последовательная проверка значения 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-значением/выражениемС 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.

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

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

 

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

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

Подведем некоторые итоги:

  • 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 -- и еще не забываем данные по внештатникам

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

 

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

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

 

Хоть со стороны может выглядит и страшно, но все равно это получше чем было изначально. Недостаток в том, что если заведут новый отдел и его сотрудников, то выражение CASE нам нужно будет дописывать, дабы сотрудники нового отдела не попали в группу «Прочие».

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

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

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

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

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

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

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

  • Выбирать детальные данные по условию 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 изначально задумывался, как инструмент для вас. Так что, все в ваших руках и желании, не отпускайте руки.

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

Собственно, если вы поняли, что такое группировка, то с HAVING ничего сложного нет. H AVING – чем-то подобен WHERE, только если WHERE-условие применяется к детальным данным, то HAVING-условие применяется к уже сгруппированным данным. По этой причине в условиях блока 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, то сосредоточьтесь в первую очередь, именно на изучении базовых конструкций, т.к. владея базой, все остальное вам понять будет гораздо легче, и к тому же самостоятельно. Вам в первую очередь, как бы нужно объемно понять возможности языка SQL, т.е. какого рода операции он вообще позволяет совершить над данными. Донести до начинающих информацию в объемном виде – это еще одна из причин, почему я буду показывать только самые главные (железные) конструкции.

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

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

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

CASE
– это инструкция, которая проверяет список условий и возвращает соответствующий результат. Если говорить в целом о программировании, то CASE – это что-то вроде многократного использования конструкции IF-ELSE, во многих языках есть похожая конструкция SWITCH, так вот CASE, как я уже отметил, делает примерно то же самое.

Выражение CASE можно использовать практически в любой инструкции T-SQL, где есть возможность использовать допустимые выражения, например: SELECT, UPDATE, WHERE, SET и даже в ORDER BY.

CASE имеет два так называемых формата:

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

Синтаксис CASE в Transact-SQL

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

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

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

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

  • input_expression — выражение, которое необходимо проверить в простом формате CASE;
  • WHEN when_expression — выражение, с которым сравнивается input_expression, в случае с простым форматом. Тип данных when_expression должен быть такой же, как и у input_expression, или хотя бы неявно преобразовываться;
  • THEN result_expression — выражение, которое будет возвращено, если текущее условие выполняется;
  • ELSE else_result_expression – дополнительный параметр ELSE, который предназначен для случаев, когда ни одно из перечисленных в CASE условий не выполнилось. Это необязательный параметр. Если ELSE не указано, а условия не выполнились, вернётся NULL;
  • WHEN Boolean_expression — логическое выражение, используемое в поисковом формате CASE, которое служит для вычисления результата. Это своего рода проверочное условие и таких условий может быть несколько.

CASE возвращает результат первого выражения (THEN result_expression), условие которого выполнилось, т.е. W HEN возвращает TRUE. Таким образом, если CASE содержит несколько эквивалентных условий WHEN, которые будут возвращать TRUE, вернется результат (указанный в THEN) первого выражения.

Тип данных
возвращаемого результата выражением CASE, будет соответствовать наиболее приоритетному типу данных из набора типов в выражениях result_expressions и else_result_expression.

Примеры CASE в Transact-SQL

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

Исходные данные для примеров

Допустим, что у нас есть таблица с товарами, она имеет следующую структуру и данные.

  
 --Создание таблицы
 CREATE TABLE TestTable(
 [ProductId] [INT] IDENTITY(1,1) NOT NULL,
 [ProductName] [VARCHAR](100) NOT NULL,
 [Price] [Money] NULL
 )
 GO
 --Добавление строк в таблицу
 INSERT INTO TestTable(ProductName, Price)
 VALUES ('Системный блок', 300),
 ('Монитор', 200),
 ('Клавиатура', 100),
 ('Мышь', 50),
 ('Принтер', 200)
 GO
 --Выборка данных
 SELECT * FROM TestTable

Скриншот 2

Пример простого выражения CASE в инструкции SELECT

В этом примере мы проверяем значение столбца ProductId, если оно равняется одному из перечисленных значений в выражении WHEN, то будет выводиться соответствующее значение из выражения THEN. Если нам встретится значение, которого мы не указали, CASE вернет пусто, т.е. значение из ELSE.

  
 --Простое выражение CASE
 SELECT CASE ProductId WHEN 1 THEN 'Один'
 WHEN 2 THEN 'Два'
 WHEN 3 THEN 'Три'
 WHEN 4 THEN 'Четыре'
 WHEN 5 THEN 'Пять'
 ELSE '' 
 END AS IdText,
 
 ProductId, ProductName, Price

 FROM TestTable

Скриншот 3

Пример поискового выражения CASE в инструкции SELECT

Следующий запрос эквивалентен предыдущему, но в нем используется уже поисковый формат CASE.

  
 --Пример поискового выражения CASE
 SELECT CASE WHEN ProductId = 1 THEN 'Один'
 WHEN ProductId = 2 THEN 'Два'
 WHEN ProductId = 3 THEN 'Три'
 WHEN ProductId = 4 THEN 'Четыре'
 WHEN ProductId = 5 THEN 'Пять'
 ELSE '' 
 END AS IdText,
 
 ProductId, ProductName, Price

 FROM TestTable

Скриншот 4

Как видим, результат точно такой же.

Давайте немного усложним запрос, добавим в него некий анализ цены (Price). Также я здесь покажу, что в случае, если по факту у нас может выполниться несколько условий, результат будет возвращен первого выражения с TRUE ( т.е. условие выполнилось
), выражения, идущие после, не обрабатываются.

  
 --Пример поискового выражения CASE
 SELECT CASE WHEN Price > 100 THEN 'Больше 100'
 WHEN Price = 100 THEN 'Равно 100'
 WHEN Price < 100 THEN 'Меньше 100'
 WHEN Price = 300 AND ProductId = 1 THEN 'Цена равна 300 и Id равен 1'
 ELSE 'Нет подходящего условия' 
 END AS IdText,
 
 ProductId, ProductName, Price

 FROM TestTable

Скриншот 5

Как видите, условие WHEN Price = 300 AND ProductId = 1 с виду выполняется, но CASE вернул результат первого выражения, после которого анализ был прекращен, и дело до следующих условий не дошло.

Пример выражения CASE в инструкции SET

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

Примечание!
Про основы программирования на языке T-SQL в Microsoft SQL Server можете почитать в материале « Основы программирования на T-SQL
».

  
 --Пример выражения CASE в инструкции SET
 DECLARE @TestVar VARCHAR

; DECLARE @Id INT = 1; SET @TestVar = CASE WHEN @Id = 1 THEN ‘Один’ WHEN @Id = 2 THEN ‘Два’ WHEN @Id = 3 THEN ‘Три’ ELSE » END; SELECT @TestVar AS TestVar

Скриншот 6

У меня на этом все, пока!

 

 

Старый добрый switch был в Java с первого дня. Мы все используем его и привыкли к нему — особенно к его причудам (кого-нибудь еще раздражает break?). Но начиная с Java 12
, ситуация начала меняться: switch вместо оператора стал выражением:

 

boolean result = switch(ternaryBool) {
    case TRUE -> true;
    case FALSE -> false;
    case FILE_NOT_FOUND -> throw new UncheckedIOException(
        "This is ridiculous!",
        new FileNotFoundException());
    default -> throw new IllegalArgumentException("Seriously?!");
};

Результат работы switch-выражения теперь можно сохранять в переменную; ушла необходимость использовать break
в каждой ветке case
благодаря лямбда-синтаксису и многое другое.

Когда дело доходит до switch
после Java 14, необходимо выбрать стиль его использования:

 

  • оператор или выражение (с Java 14)
  • двоеточия или стрелки (с Java 14)
  • метки или шаблоны (3-й превью в Java 19)
    В этом руководстве я расскажу обо всем, что необходимо знать о switch-выражениях, и как их лучше всего использовать в современной Java.

Недостатки оператора switch

Прежде, чем мы перейдем к обзору нововведений, давайте рассмотрим один пример кода. Допустим, мы столкнулись с «ужасным» тернарным boolean
и хотим преобразовать его в обычный boolean
. Вот один из способов сделать это:

 

boolean result;
switch(ternaryBool) {
    case TRUE:
        result = true;
        break;
    case FALSE:
        result = false;
        break;
    case FILE_NOT_FOUND:
        // объявление переменной для демонстрации проблемы в default
        var ex = new UncheckedIOException("This is ridiculous!",
             new FileNotFoundException());
        throw ex;
    default:
        // А вот и проблема: мы не можем объявить еще одну переменную с именем ex
        var ex2 = new IllegalArgumentException("Seriously?!");
        throw ex2;
}

Реализация данного кода хромает: наличие break
в каждой ветке, которые легко забыть; можно не учесть все возможные значения ternaryBool
(забыть реализовать какой-то case); с переменной result
не все гладко — область видимости не соответствует ее использованию; нельзя объявить в разных ветках переменные с одинаковым именем. Согласитесь, что данное решение выглядит крайне громоздко и неудобно — тут явно есть, что улучшить.

А вот пример попроще, демонстрирующий похожие проблемы:

 

int result;
switch (number) {
    case 1:
        result = callMethod("one");
        break;
    case 2:
        result = callMethod("two");
        break;
    default:
        result = callMethod("many");
        break;
}

Давайте попробуем устранить все недостатки, поместив switch в отдельный метод:

 

private static boolean toBoolean(Bool ternaryBool) {
    switch(ternaryBool) {
        case TRUE: return true;
        case FALSE: return false;
        case FILE_NOT_FOUND:
            throw new UncheckedIOException("This is ridiculous!",
                  new FileNotFoundException());
        // без default метод не скомпилируется
        default:
            throw new IllegalArgumentException("Seriously?!");
      }
}

Так намного лучше: отсутствует фиктивная переменная result, нет break, загромождающих код и сообщений компилятора об отсутствии default (даже если в этом нет необходимости, как в данном случае).

Но если подумать, то мы не обязаны создавать методы только для того, чтобы обойти неуклюжую особенность языка. И это даже без учёта, что такой рефакторинг не всегда возможен. Нет, нам нужно решение получше!

 

Представляем switch-выражения!

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

 

boolean result = switch(ternaryBool) {
    case TRUE -> true;
    case FALSE -> false;
    case FILE_NOT_FOUND -> throw new UncheckedIOException(
        "This is ridiculous!",
        new FileNotFoundException());
    // в ветке `default` уже нет необходимости
    default -> throw new IllegalArgumentException("Seriously?!");
};

Я думаю, что это довольно очевидно: если ternartBool равен TRUE, то result будет присвоено true, а FALSE становится false.

Сразу возникают две мысли:

 

  • switch теперь может иметь результат
  • какие возможности предоставляют стрелки?

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

 

Выражение vs оператора

Возможно, вы удивлены, что switch теперь является выражением. А чем же он был до этого? До Java 12 switch был оператором — императивной конструкцией, управляющей исполняющим потоком.

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

Разница состоит в том, что if просто выполняет соответствующий блок, тогда как тернарный оператор возвращает какой-то результат:

 

if(condition) {
    result = doThis();
} else {
    result = doThat();
}

result = condition ? doThis() : doThat();

То же самое и у switch: до Java 12, если вы хотели вычислить значение и сохранить результат, то должны были либо присвоить его переменной, либо вернуть из метода, созданного специально для оператора switch.

Теперь же результат вычислений оператора switch может быть присвоен переменной.

Еще одно отличие заключается в том, что поскольку выражение является частью оператора, то оно должно заканчиваться точкой с запятой, в отличие от классического оператора switch.

 

Стрелка vs двоеточия

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

 

boolean result = switch (ternaryBool) {
    case TRUE:
        yield true;
    case FALSE:
        yield false;
    case FILE_NOT_FOUND:
        throw new UncheckedIOException(
            "This is ridiculous!",
            new FileNotFoundException());
    default:
        throw new IllegalArgumentException("Seriously?!");
};

Обратите внимание, что вам нужно использовать новое ключевое слово yield, чтобы вернуть значение из ветки case (этот синтаксис появился в Java 13. В Java 12 вместо yield применялся break, т. е. break true вместо yield true, что выглядело странно).

Исторически сложилось, что метки с двоеточием определяют точку входа в блок операторов. С этого места начинается выполнение всего кода ниже, даже когда встречается другая метка (при отсутствии break). Механизм такой работы известен, как сквозной переход к следующему case. Для его прерывания нужен break или return.

Использование же стрелки позволяет выполнять только блок справа от нее. И никакого «проваливания».

Александр

Здравствуйте, меня зовут Александр, уже более 10 лет я занимаюсь ремонтом компьютером, этот сайт я создал чтобы делиться полезной и практической информацией с вами! Буду благодарен, если вы опишите свой опыт или мнение в комментарии, надеюсь, что данная информация принесёт только пользу

Оцените автора
WindowsComp.ru
Добавить комментарий