Написать SQL-код с несколькими условиями может быть не так просто, особенно если вам нужно выполнить целый ряд проверок.
Например, выражение альтернативы if () else if () else {} обрабатывает все условные операторы SQL. Если первое условие выполняется, то запрос прекращает свое выполнение и возвращает значение. Значение, которое было указано в else, возвращается только в случае, если ни одно условие не выполнилось.
В этой статье мы с вами рассмотрим:
- Что такое оператор SQL CASE и как он работает
- Как решить задачу с помощью оператора SQL CASE
- Что означают такие немаловажные термины, такие как 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 для того, чтобы выполнить следующее:
- Проверить, что ManagerID не равен NULL
- Сопоставить ManagerID из таблицы company и ID из таблицы company и вернуть Name в качестве значения ManagerName
- Сделать так, чтобы, если 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.
