Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 25: использование условий при модификации данных
Решение для MS SQL сервер построено на той же логике. Оно даже чуть проще в реализации, т.к. MS SQL Server не запрещает в одном запросе обновлять данные в таблице и читать данные из этой же таблицы — поэтому нет необходимости «оборачивать» выборку в строке 5 в подзапрос.
Решение для Oracle отличается от решения для MS SQL Server только синтаксисом увеличения даты на нужное количество месяцев (строки 11-12). Как и MS SQL Server, Oracle не запрещает в одном запросе обновлять данные в таблице и читать данные из этой же таблицы.
|
Oracle |
|
Решение 2.3.5.b |
|
||
|
1 |
|
UPDATE |
"subscriptions" |
|
|
|
2 |
|
SET |
"sb_finish" = CASE |
|
|
|
3 |
|
|
|
WHEN "sb_is_active" = 'Y' |
|
|
4 |
|
|
|
AND EXISTS (SELECT |
"int"."sb_subscriber" |
|
5 |
|
|
|
FROM |
"subscriptions" "int" |
|
6 |
|
|
|
WHERE "int"."sb_is_active" = 'Y' |
|
|
7 |
|
|
|
AND "int"."sb_subscriber" = |
|
|
8 |
|
|
|
|
"subscriptions"."sb_subscriber" |
|
9 |
|
|
|
GROUP |
BY "int"."sb_subscriber" |
|
10 |
|
|
|
HAVING |
COUNT("int"."sb_book") > 2) |
11 |
|
THEN (SELECT ADD_MONTHS("sb_finish", 2) FROM "DUAL") |
12 |
|
ELSE (SELECT ADD_MONTHS("sb_finish", 1) FROM "DUAL") |
13 |
|
END |
Решение 2.3.5.c{200}.
В отличие от предыдущих задач данного примера, здесь для каждой СУБД решения будет принципиально разными. Самый универсальный вариант реализован для Oracle (его можно с минимальными синтаксическими доработками реали-
зовать и в MySQL, и в MS SQL Server).
|
MySQL |
|
Решение 2.3.5.c |
||
|
1 |
|
UPDATE `subscribers` |
||
2 |
|
SET |
`s_name` = CONCAT(`s_name`, |
||
3(
4SELECT `postfix`
5 |
|
FROM |
(SELECT `s_id`, |
|
|
6 |
|
|
|
@x := IFNULL |
|
7 |
|
|
|
( |
|
8 |
|
|
|
(SELECT COUNT(`sb_book`) |
|
9 |
|
|
|
FROM |
`subscriptions` AS `int` |
10 |
|
|
|
WHERE `int`.`sb_is_active` = 'Y' |
|
11 |
|
|
|
|
AND `int`.`sb_subscriber` = |
12 |
|
|
|
|
`ext`.`sb_subscriber` |
13 |
|
|
|
GROUP |
BY `int`.`sb_subscriber`), 0 |
14 |
|
|
|
), |
|
15 |
|
|
|
CASE |
|
16 |
|
|
|
WHEN @x > 5 THEN |
|
17 |
|
|
|
(SELECT CONCAT(' [', @x, '] [RED]')) |
|
18 |
|
|
|
WHEN @x >= 3 AND @x <= 5 THEN |
|
19 |
|
|
|
(SELECT CONCAT(' [', @x, '] [YELLOW]')) |
|
20 |
|
|
|
ELSE (SELECT CONCAT(' [', @x, '] [GREEN]')) |
|
21 |
|
|
|
END AS `postfix` |
|
22 |
|
|
FROM |
`subscribers` |
|
23 |
|
|
LEFT JOIN `subscriptions` AS `ext` |
||
24 |
|
|
|
ON `s_id` = `sb_subscriber` |
|
25 |
|
|
GROUP |
BY `sb_subscriber`) AS `data` |
|
26WHERE `data`.`s_id` = `subscribers`.`s_id`)
27)
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 205/545
Пример 25: использование условий при модификации данных
Начнём рассмотрение с наиболее глубоко вложенного подзапроса (строки 6- 14). Это — коррелирующий подзапрос, выполняющий подсчёт книг, находящихся на руках у того читателя, который в данный момент анализируется подзапросом в строках 5-25. Результат выполнения подзапроса помещается в переменную @x:
s_id |
@x |
2 |
0 |
1 |
0 |
3 |
3 |
4 |
4 |
Выражение CASE в строках 15-21 на основе значения переменной @x определяет суффикс, который необходимо добавить к имени читателя:
s_id |
@x |
postfix |
2 |
0 |
[0] [GREEN] |
1 |
0 |
[0] [GREEN] |
3 |
3 |
[3] [YELLOW] |
4 |
8 |
[4] [YELLOW] |
Коррелирующий подзапрос в строках 3-27 передаёт в функцию CONCAT суффикс, соответствующий идентификатору читателя, имя которого обновляется (в строке 26 производится соответствующее сравнение). Имя читателя заменяется на результат работы функции CONCAT, и таким образом получается итоговый результат.
Для лучшего понимания данного решения приведём модифицированный запрос, который ничего не обновляет, но показывает всю необходимую информацию:
MySQL Решение 2.3.5.c (модифицированный запрос)
1SELECT `s_id`,
2`s_name`,
3@x := IFNULL
4 |
|
( |
|
5 |
|
(SELECT COUNT(`sb_book`) |
|
6 |
|
FROM |
`subscriptions` AS `int` |
7 |
|
WHERE |
`int`.`sb_is_active` = 'Y' |
8 |
|
AND |
`int`.`sb_subscriber` = |
9 |
|
|
`ext`.`sb_subscriber` |
10 |
|
GROUP |
BY `int`.`sb_subscriber`), 0 |
11 |
), |
|
|
12CASE
13WHEN @x > 5 THEN
14(SELECT CONCAT(' [', @x, '] [RED]'))
15WHEN @x >= 3 AND @x <= 5 THEN
16(SELECT CONCAT(' [', @x, '] [YELLOW]'))
17ELSE (SELECT CONCAT(' [', @x, '] [GREEN]'))
18END AS `postfix`
19 FROM `subscribers`
20LEFT JOIN `subscriptions` AS `ext`
21ON `s_id` = `sb_subscriber`
22GROUP BY `sb_subscriber`
Результат выполнения этого модифицированного запроса:
s_id |
s_name |
@x |
postfix |
2 |
Петров П.П. |
0 |
[0] [GREEN] |
1 |
Иванов И.И. |
0 |
[0] [GREEN] |
3 |
Сидоров С.С. |
3 |
[3] [YELLOW] |
4 |
Сидоров С.С. |
4 |
[4] [YELLOW] |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 206/545
Пример 25: использование условий при модификации данных
MS SQL Server не позволяет использовать переменные так же гибко, как MySQL — существуют ограничения на одновременную выборку данных и изменение значения переменной, а также требования по предварительному объявлению переменных.
Однако MS SQL Server поддерживает общие табличные выражения, куда мы и перенесём логику определения того, сколько книг в настоящий момент находится на руках у каждого читателя (строки 1-10). В отличие от решения для MySQL здесь вместо коррелирующего подзапроса используется подзапрос как источник данных (строки 4-8), возвращающий информацию только о книгах, находящихся на руках у читателей.
MS SQL Решение 2.3.5.c
1WITH [prepared_data]
2AS (SELECT [s_id], COUNT([sb_subscriber]) AS [x]
3 |
|
FROM |
[subscribers] |
4 |
|
|
LEFT JOIN ( |
|
|
|
|
5 |
|
|
SELECT [sb_subscriber] |
6 |
|
|
FROM [subscriptions] |
7 |
|
|
WHERE [sb_is_active] = 'Y' |
8 |
|
|
) AS [active_only] |
9 |
|
|
ON [s_id] = [sb_subscriber] |
10GROUP BY [s_id])
11UPDATE [subscribers]
12 |
|
SET |
[s_name] = |
|
13 |
|
|
(SELECT |
|
14 |
|
|
CASE |
|
15 |
|
|
WHEN |
[x] > 5 |
16 |
|
|
THEN (SELECT CONCAT([s_name], ' [', [x], '] [RED]')) |
|
17 |
|
|
WHEN |
[x] >= 3 AND [x] <= 5 |
18 |
|
|
THEN (SELECT CONCAT([s_name], ' [', [x], '] [YELLOW]')) |
|
19 |
|
|
ELSE |
(SELECT CONCAT([s_name], ' [', [x], '] [GREEN]')) |
20 |
|
|
END |
|
21 |
|
|
FROM |
[prepared_data] |
22 |
|
|
WHERE |
[subscribers].[s_id] = [prepared_data].[s_id]) |
|
|
|
|
|
Результат работы общего табличного выражения таков:
s_id |
x |
1 |
0 |
2 |
0 |
3 |
3 |
4 |
4 |
Коррелирующий подзапрос в строках 13-22 на основе значения колонки x формирует значение суффикса имени соответствующего читателя. Так получается итоговый результат.
Если есть потребность избавиться от коррелирующего подзапроса в строках 13-22, можно переписать основную часть решения (общее табличное выражение остаётся таким же) с использованием JOIN.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 207/545
Пример 25: использование условий при модификации данных
MS SQL 
Решение 2.3.5.c (вариант решения с JOIN вместо подзапроса)
1WITH [prepared_data]
2AS (SELECT [s_id], COUNT([sb_subscriber]) AS [x]
3 |
|
FROM |
[subscribers] |
4 |
|
|
LEFT JOIN ( |
5 |
|
|
SELECT [sb_subscriber] |
6 |
|
|
FROM [subscriptions] |
7 |
|
|
WHERE [sb_is_active] = 'Y' |
8 |
|
|
) AS [active_only] |
9 |
|
|
ON [s_id] = [sb_subscriber] |
10GROUP BY [s_id])
11UPDATE [subscribers]
12 |
|
SET |
[s_name] = |
|
13 |
|
|
(CASE |
|
14 |
|
|
WHEN [x] > 5 |
|
15 |
|
|
THEN (SELECT |
CONCAT([s_name], ' [', [x], '] [RED]')) |
16 |
|
|
WHEN [x] >= 3 |
AND [x] <= 5 |
17 |
|
|
THEN (SELECT |
CONCAT([s_name], ' [', [x], '] [YELLOW]')) |
18 |
|
|
ELSE (SELECT CONCAT([s_name], ' [', [x], '] [GREEN]')) |
|
19 |
|
|
END) |
|
20FROM [subscribers]
21JOIN [prepared_data]
22ON [subscribers].[s_id] = [prepared_data].[s_id]
Такой вариант решения (с использованием JOIN в контексте UPDATE) является самым оптимальным, но в то же время и наименее привычным (особенно для начинающих).
Oracle не поддерживает только что рассмотренное использование JOIN в контексте UPDATE, потому от коррелирующего подзапроса в строках 2-20 избавиться не удастся.
Также Oracle не поддерживает использование общих табличных выражений в контексте UPDATE, что приводит к необходимости переноса подготовки данных в подзапрос (строки 11-19).
|
Oracle |
|
Решение 2.3.5.c |
||
|
1 |
|
UPDATE |
"subscribers" |
|
|
2 |
|
SET |
"s_name" = |
|
3(SELECT
4CASE
5WHEN "x" > 5
6THEN (SELECT "s_name" || ' [' || "x" || '] [RED]' FROM "DUAL")
7WHEN "x" >= 3 AND "x" <= 5
8THEN (SELECT "s_name" || ' [' || "x" || '] [YELLOW]' FROM "DUAL")
9ELSE (SELECT "s_name" || ' [' || "x" || '] [GREEN]' FROM "DUAL")
10END
11 |
|
FROM (SELECT |
"s_id", |
12 |
|
|
COUNT("sb_subscriber") AS "x" |
13 |
|
FROM |
"subscribers" |
14 |
|
LEFT |
JOIN |
15 |
|
|
(SELECT "sb_subscriber" |
|
|
|
|
16 |
|
|
FROM "subscriptions" |
17 |
|
|
WHERE "sb_is_active" = 'Y') |
18 |
|
|
ON "s_id" = "sb_subscriber" |
|
|
|
|
19GROUP BY "s_id") "prepared_data"
20WHERE "subscribers"."s_id" = "prepared_data"."s_id")
Ещё одно ограничение Oracle проявляется в том, что здесь функция CONCAT может принимать только два параметра (а нам нужно передать четыре), но мы можем обойти это ограничение с использованием оператора конкатенации строк ||.
Ранее было отмечено, что решение для Oracle является самым универсальным и может быть легко перенесено в другие СУБД. Продемонстрируем это.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 208/545
Пример 25: использование условий при модификации данных
MySQL Решение 2.3.5.c (получено на основе решения для Oracle)
1 |
|
UPDATE |
`subscribers` |
2 |
|
SET |
`s_name` = |
3(SELECT
4CASE
5WHEN `x` > 5 THEN
6(SELECT CONCAT(`s_name`, ' [', `x`, '] [RED]'))
7WHEN `x` >= 3 AND `x` <= 5 THEN
8(SELECT CONCAT(`s_name`, ' [', `x`, '] [YELLOW]'))
9ELSE (SELECT CONCAT(`s_name`, ' [', `x`, '] [GREEN]'))
10END
11 |
|
FROM |
(SELECT |
`s_id`, |
12 |
|
|
|
COUNT(`sb_subscriber`) AS `x` |
13 |
|
|
FROM |
`subscribers` |
14 |
|
|
LEFT |
JOIN |
15 |
|
|
|
(SELECT `sb_subscriber` |
16 |
|
|
|
FROM `subscriptions` |
17 |
|
|
|
WHERE `sb_is_active` = 'Y') AS `active_only` |
18 |
|
|
|
ON `s_id` = `sb_subscriber` |
19 |
|
|
GROUP |
BY `s_id`) AS `prepared_data` |
20 |
|
WHERE |
`subscribers`.`s_id` = `prepared_data`.`s_id`) |
|
MS SQL Решение 2.3.5.c (получено на основе решения для Oracle)
1 |
|
UPDATE |
[subscribers] |
2 |
|
SET |
[s_name] = |
|
|
|
|
3(SELECT
4CASE
5WHEN [x] > 5
6THEN (SELECT CONCAT([s_name], ' [', [x], '] [RED]'))
7WHEN [x] >= 3 AND [x] <= 5
8THEN (SELECT CONCAT([s_name], ' [', [x], '] [YELLOW]'))
9ELSE (SELECT CONCAT([s_name], ' [', [x], '] [GREEN]'))
10END
11 |
|
FROM (SELECT |
[s_id], |
12 |
|
|
COUNT([sb_subscriber]) AS [x] |
13 |
|
FROM |
[subscribers] |
14 |
|
LEFT |
JOIN |
15 |
|
|
(SELECT [sb_subscriber] |
16 |
|
|
FROM [subscriptions] |
17 |
|
|
WHERE [sb_is_active] = 'Y') AS [active_only] |
18 |
|
|
ON [s_id] = [sb_subscriber] |
19GROUP BY [s_id]) AS [prepared_data]
20WHERE [subscribers].[s_id] = [prepared_data].[s_id])
Задание 2.3.5.TSK.A: добавить в базу данных читателей с именами «Сидоров С.С.», «Иванов И.И.», «Орлов О.О.»; если читатель с таким именем уже существует, добавить в конец имени нового читателя порядковый номер в квадратных скобках (например, если при добавлении читателя «Сидоров С.С.» выяснится, что в базе данных уже есть четыре таких читателя, имя добавляемого должно превратиться в «Сидоров С.С. [5]»).
Задание 2.3.5.TSK.B: обновить все имена авторов, добавив в конец имени « [+]», если в библиотеке есть более трёх книг этого автора, или добавив в конец имени « [-]» в противном случае.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 209/545