img

Как использовать оператор SQL CASE – на примере задачи

Написать SQL-код с несколькими условиями может быть не так просто, особенно если вам нужно выполнить целый ряд проверок.

Например, выражение альтернативы if () else if () else {} обрабатывает все условные операторы SQL. Если первое условие выполняется, то запрос прекращает свое выполнение и возвращает значение. Значение, которое было указано в else, возвращается только в случае, если ни одно условие не выполнилось.

В этой статье мы с вами рассмотрим: 

  1. Что такое оператор SQL CASE и как он работает
  2. Как решить задачу с помощью оператора SQL CASE
  3. Что означают такие немаловажные термины, такие как ORDER BY, LIMIT, OFFSET, LEFT JOIN и ALIAS.

Что такое оператор SQL CASE?

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

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

Оператор SQL CASE имеет следующий синтаксис:

CASE
    WHEN conditional_statement1 THEN result1
    .
    .
    .
    .
    WHEN condition_statementN THEN resultN
    
    ELSE result
END;

Синтаксис оператора SQL CASE

Когда вы используете оператор CASE, следующим должен быть WHEN, затем THEN и результат, который возвращается при выполнении первого условия. Если первое условие не выполняется, то проверяются следующие условия вплоть до n-го (или последнего) условия. Если они также не выполняются, то выполняется условие ELSE.

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

Задача

Здесь мы рассмотрим реальный пример, чтобы помочь вам научится решать задачи в SQL с помощью оператора CASE.

С этой задачей я столкнулся на Coderbyte. Это платформа для отработки задач по программированию. Ее было не так просто решить, и сейчас я разберу решение этой задачи пошагово.

В чем заключается задача?

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

Вам нужно построить запрос таким образом, чтобы найти этого сотрудника и вернуть его строку. Также вы должны заменить значение DivisionID соответствующим значением DivisionName из таблицы company_divisions. Затем, если значение ID есть в таблице и не равен NULL, то вам нужно заменить это значение на значение ManagerName.

Какую проблему решает оператор SQL CASE в этой задаче?

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

  1. Проверить, что ManagerID не равен NULL
  2. Сопоставить ManagerID из таблицы company и ID из таблицы company и вернуть Name в качестве значения ManagerName
  3. Сделать так, чтобы, если Name не возвращается, то в качестве значения ManagerName по умолчанию возвращается имя Susan Wall

Вот, что должно получиться:

ID

Name

DivisionName

ManagerName

Salary

222

Mark Red

Sales

Susan Wall

86000

А ниже представлены данные, которые вам нужны для решения этой задачи:

Таблица 1: company

ID

Name

DivisionID

ManagerID

Salary

356

Daniel smith

100

133

40000

122

Arnold Sully

101

null

60000

467

Lisa Roberts

100

null

80000

112

Mary Dial

105

467

65000

775

Dennis Front

103

null

90000

111

Larry Weis

104

35534

75000

222

Mark Red

102

133

86000

577

Robert Niger

105

12353

76000

133

Susan Wall

105

577

110000

Таблица 2: company_divisions

ID 

 

DivisionName 

100 

 

Accounting 

101 

 

IT 

102 

 

Sales 

103 

 

Marketing 

104 

 

Engineering 

105 

 

Customer Support 

Как решить задачу с помощью оператора SQL CASE?

Здесь мы рассмотрим пошаговый процесс решения этой задачи.

Шаг 1: получение строки сотрудника с третьей по величине заработной платой

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

Выглядеть это будет примерно так:

SELECT *

FROM company

ORDER BY salary DESC limit 1 offset 2;

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

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

ID

Name

DivisionID

ManagerID

Salary

222

Mark Red

102

133

86000

Итак, что же творится в этом запросе?

SELECT: команда SELECT со звездочкой (*) (также известной как подстановочный знак) извлекает все столбцы из таблицы company.

ORDER BY: команда ORDER BY упорядочивает столбцы в порядке возрастания или убывания. По умолчанию SQL упорядочивает по возрастанию (ASC), но мы упорядочим столбец заработной платы по убыванию (DESC). Мы делаем именно так, поскольку нам нужен убывающий список заработных плат, от самой высокой до самой низкой, то есть от 110 000 до 40 000.

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

offset: оператор offset помогает нам указать количество строк, которые необходимо пропустить, прежде чем возвращать строки из запроса. С помощью offset мы можем пропустить две строки с самыми высокими заработными платами (Susan Wall и Dennis Front) и вернуть строку с третьей по величине заработной платой (Mark Red).

Шаг 2: замена DivisionID на DivisionName

Теперь нам нужно изменить запрос так, чтобы он выбирал только нужные нам столбцы – ID, Name, ManagerID, DivisionName и Salary. И затем заменить столбец Division ID соответствующим столбцом DivisionName из таблицы company_divisions.

Вы можете сделать это так:

SELECT c.ID, c.Name, c.ManagerID, c.salary, cd.DivisionName

FROM company as c

LEFT JOIN company_divisions as cd ON c.DivisionId = cd.id

ORDER BY salary DESC limit 1 offset 2;

Выбор столбцов и объединение с таблицей company_division

Вот результат:

ID

Name

DivisionName

ManagerID

Salary

222

Mark Red

Sales

133

86000

Давайте обсудим, что же творится в запросе выше:

LEFT JOIN: так как записи возвращаются с левой стороны (company), то мы сопоставляем их с правой стороны (company_division) с помощью company_division.id и company.DivisionID.

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

as (alias): alias используется для задания временного псевдонима таблицы. Таким образом, с учетом того, что у таблицы company есть псевдоним (c), вместо company.name мы можем использовать c.name. Использование таких псевдонимов помогает улучшить читаемость кода. 

Шаг 3: Замена ManagerID на ManagerName

За основу возьмем результат запроса из шага 2. Мы будем использовать оператор CASE, который мы уже изучили, для того, чтобы добавить условия: равен ли ManagerID NULL и существует ли ManagerID. 

В первую очередь нам нужно проверить, не является ли значение company.ManagerID нулевым, и убедиться, что ID существует в таблице. Для этого мы применим оператор CASE.

CASE WHEN c.ManagerID IS NOT NULL 

AND c.ManagerID = c.ID

Первый шаг оператора CASE

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

CASE WHEN c.ManagerID IS NOT NULL 

AND c.ManagerID = c.ID

THEN Name ELSE 'Susan Wall' END AS 'ManagerName'

Готовый оператор CASE для решения задачи

И наконец, мы должны добавить блок CASE в уже имеющийся фрагмент кода, который у нас получился на шаге 2. И теперь он будет выглядеть примерно так:

SELECT c.ID, c.Name, c.salary, cd.DivisionName

CASE WHEN c.ManagerID IS NOT NULL 

AND c.ManagerID = c.ID

THEN Name ELSE 'Susan Wall' END AS 'ManagerName'

FROM company as c

LEFT JOIN company_divisions as cd ON c.DivisionId = cd.id

ORDER BY salary DESC limit 1 offset 2;

Оператор SQL CASE вместе с операторами SELECT и JOIN

Результат шага 3 – это то, что мы и хотели получить, а именно строку сотрудника с третьей по величине заработной платой. 

ID

Name

DivisionName

ManagerName

Salary

222

Mark Red

Sales

Susan Wall

86000

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

Я надеюсь, что эта статья помогла понять, что такое оператор SQL CASE и как можно с его помощью решать реальные задачи.

Также мы рассмотрели и другие команды SQL, такие как SELECT, ORDER BY, LIMIT, OFFSET, LEFT JOIN и ALIAS. 

Ссылка
скопирована
Программирование
Скидка 25%
Python-программист с нуля
Стань разработчиком на одном из самых популярных языков программирования.
Получи бесплатный
вводный урок!
Пожалуйста, укажите корректный e-mail
отправили вводный урок на твой e-mail!
Получи все материалы в telegram и ускорь обучение!
img
Еще по теме:
img
  Работа со сценариями PowerShell может оказаться захватывающим приключением. Ведь он предоставляет вам чистый интерфейс для т
img
  Давайте узнаем, какие известные языки описания сценариев чаще всего используют системные администраторы и разработчики. Чаще в
img
Неважно, новичок вы или опытный разработчик программного обеспечение, вы так или иначе допускаете ошибки при написании кода.  Вс
img
Когда мы начинаем разрабатывать какой-либо Python-проект, который выходит за пределы обычных сценариев, мы обычно начинаем привл
img
  В современном мире рынком правят мобильные и веб-приложения. Преобладающая позиция – ориентация в первую очередь на мобильные
img
Модуль – это функция или набор похожих функций. Они собраны вместе внутри одного файла и содержат код. Когда в более крупном при
Комментарии
ЛЕТНИЕ СКИДКИ
40%
50%
60%
До конца акции: 30 дней 24 : 59 : 59