Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 25: использование условий при модификации данных
MySQL |
Решение 2.3.5.b I |
|
|
|
1 |
UPDATE 'subscriptions' AS 'ext' |
|
|
|
2 |
SET |
'sb finish' = CASE |
|
|
3 |
|
WHEN 'sb is active' = 'Y' |
|
|
4 |
|
AND EXISTS (SELECT 'int' 'sb subscriber' |
||
5 |
|
FROM |
(SELECT 'sb subscriber', |
|
6 |
|
|
|
'sb book' |
7 |
|
|
|
'sb is active' |
8 |
|
|
FROM |
'subscriptions') AS 'int' |
9 |
|
WHERE |
'int' 'sb is active' = 'Y' |
|
10 |
|
|
AND 'int' 'sb subscriber' = |
|
11 |
|
|
'ext' 'sb subscriber' |
|
12 |
|
GROUP |
BY 'int' 'sb subscriber' |
|
13 |
|
HAVING COUNT('int' 'sb book') > 2) |
||
14 |
|
THEN (SELECT DATE ADD('sb finish', INTERVAL 2 MONTH)) |
||
15 |
|
ELSE (SELECT DATE ADD('sb finish', INTERVAL 1 MONTH)) |
||
16 |
|
END |
|
|
|
|
|
|
|
Составное условие, являющееся ядром решения поставленной задачи, представлено в строках 3-13.
Его первая часть (строка 3) проста: мы определяем значение поля.
Его вторая часть (строки 4-13) представляет собой коррелирующий подзапрос, результаты которого передаются в функцию EXISTS, т.е. нас интересует сам
факт того, вернул ли подзапрос хотя бы одну строку, или нет.
Необходимость ещё одного вложенного запроса в строках 5-8 только что была рассмотрена — так мы обходим ограничение MySQL на чтение из обновляемой таблицы.
В остальном — это классический коррелирующий подзапрос. Если выполнить его отдельно, вручную подставляя значения идентификатора читателя, получится следующая картина:
|
MySQL і |
Решение 2.3.5.b (модифицированный фрагмент) |
|
1 |
SELECT 'int' 'sb subscriber' |
||
2 |
FROM |
(SELECT 'sb subscriber', |
|
3 |
|
|
'sb book' |
4 |
|
|
'sb is active' |
5 |
|
FROM |
'subscriptions') AS 'int' |
6 |
WHERE |
'int' 'sb is active' = 'Y' |
|
7 |
|
AND 'int' 'sb subscriber' = {id читателя из основной части запроса} |
|
8 |
GROUP |
BY 'int' |
'sb subscriber' |
9 |
HAVING COUNT('int' 'sb book') > 2 |
||
|
|
|
|
Для читателей с идентификаторами 1 и 2 этот подзапрос вернёт ноль строк, а для читателей с идентификаторами 3 и 4 результат будет непустым.
Строки 14 и 15 описывают желаемое поведение СУБД в случае, когда составное условие соответственно выполнилось и не выполнилось.
MS SQL I Решение 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] AS [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 DATEADD(month, 2, [sb finish]1) |
|
12 |
|
ELSE (SELECT DATEADD(month, 1, [sb finish]1) |
|
13 |
|
END |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 215/545
Пример 25: использование условий при модификации данных
Решение для MS SQL сервер построено на той же логике. Оно даже чуть проще в реализации, т.к. MS SQL Server не запрещает в одном запросе обновлять данные в таблице и читать данные из этой же таблицы — поэтому нет необходимости «оборачивать» выборку в строке 5 в подзапрос.
Решение для Oracle отличается от решения для MS SQL Server только синтаксисом увеличения даты на нужное количество месяцев (строки 11-12). Как и MS SQL Server, Oracle не запрещает в одном запросе обновлять данные в таблице и читать данные из этой же таблицы.
|
Oracl |
і |
Решение 2.3.5.b |
I |
e |
|
|||
|
|
|
|
|
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" |
2l FROM "DUAL" |
12 |
ELSE (SELECT ADD MONTHS("sb finish" |
1) FROM "DUAL" |
13 |
END |
|
уЦ7
Решение 2.3.5.c{200}.
В отличие от предыдущих задач данного примера, здесь для каждой СУБД решения будет принципиально разными. Самый универсальный вариант реализован для Oracle (его можно с минимальными синтаксическими доработками реали-
зовать и в MySQL, и в MS SQL Server).
MySQL |
Решение 2.3.5.c |
I |
|
|
|
|
1 |
UPDATE 'subscribers' |
|
|
|
||
2 |
SET |
's |
= CONCAT('s_name', |
|
|
|
3 |
|
name' ( |
|
|
|
|
4 |
|
SELECT 'postfix' |
|
|
||
5 |
|
FROM |
(SELECT 's_id', @x := IFNULL ( |
|
||
6 |
|
|
|
(SELECT COUNT('sb_book') |
||
7 |
|
|
|
FROM |
'subscriptions' AS 'int' |
|
8 |
|
|
|
WHERE 'int'.'sb_is_active' = 'Y' |
||
9 |
|
|
|
|
AND 'int' 'sb_subscriber' = |
|
10 |
|
|
|
|
'ext' 'sb_subscriber' |
|
11 |
|
|
|
GROUP |
BY 'int' 'sb_subscriber'), 0 |
|
12 |
|
|
|
), CASE |
|
|
13 |
|
|
|
WHEN @x > 5 THEN |
|
|
14 |
|
|
|
(SELECT CONCAT(' [', @x, |
'] [RED]')) |
|
15 |
|
|
|
WHEN @x >= 3 AND @x <= 5 |
THEN |
|
|
|
|
|
|||
16 |
|
|
|
(SELECT CONCAT(' [', @x, |
'] [YELLOW]')) |
|
|
|
|
|
|||
17 |
|
|
|
ELSE (SELECT CONCAT(' [', @x, '] [GREEN]')) |
||
18 |
|
|
|
|||
|
|
|
END AS 'postfix' |
|
||
19 |
|
|
|
|
||
|
|
FROM |
'subscribers' |
|
|
|
20 |
|
|
|
|
||
|
|
LEFT JOIN 'subscriptions' AS 'ext' |
||||
21 |
|
|
||||
|
|
|
ON 's_id' = 'sb_subscriber' |
|
||
22 |
|
|
|
|
||
|
|
GROUP |
BY 'sb subscriber') AS 'data' |
|||
23 |
|
|
||||
|
|
|
|
|
|
|
24 |
|
|
|
|
|
|
25 |
|
|
|
|
|
|
26 |
|
WHERE |
'data' 's_id' = 'subscribers' 's_id') |
|||
27 |
|
) |
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 216/545
Пример 25: использование условий при модификации данных
Начнём рассмотрение с наиболее глубоко вложенного подзапроса (строки 6- 14). Это — коррелирующий подзапрос, выполняющий подсчёт книг, находящихся на руках у того читателя, который в данный момент анализируется подзапросом в строках 5-25. Результат выполнения подзапроса помещается в переменную @x:
s_id |
@x |
2 |
0 |
1 |
0 |
3 |
3 |
4 |
4 |
Выражение CASE в строках 15-21 на основе значения переменной @х определяет суффикс, который необходимо добавить к имени читателя:
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, и таким образом получается итоговый резуль-
тат.
Для лучшего понимания данного решения приведём модифицированный запрос, который ничего не обновляет, но показывает всю необходимую информацию:
Решение 2.3.5.С
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'
19FROM 'subscribers'
20LEFT JOIN 'subscriptions' AS 'ext'
21ON 's id' = 'sb subscriber'
22GROUP BY 'sb subscriber'
Результат выполнения этого модифицированного запроса:
s_id |
s_name |
@Х |
postfix |
2 |
Петров П.П. |
0 |
[0] [GREEN] |
1 |
Иванов И.И. |
0 |
[0] [GREEN] |
3 |
Сидоров С.С. |
3 |
[3] [YELLOW] |
4 |
Сидоров С.С. |
4 |
[4] [YELLOW] |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 217/545
Пример 25: использование условий при модификации данных
MS SQL Server не позволяет использовать переменные так же гибко, как MySQL — существуют ограничения на одновременную выборку данных и изменение значения переменной, а также требования по предварительному объявлению переменных.
Однако MS SQL Server поддерживает общие табличные выражения, куда мы и перенесём логику определения того, сколько книг в настоящий момент находится на руках у каждого читателя (строки 1-10). В отличие от решения для MySQL здесь вместо коррелирующего подзапроса используется подзапрос как источник данных (строки 4-8), возвращающий информацию только о книгах, находящихся на руках у читателей.
MS SQL I |
Решение 2.3.5.C |
I |
|
|
|
|
|
|
1 |
WITH [prepared_data] |
|
|
|
|
|
||
2 |
|
AS (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] |
|
|
||
10 |
|
GROUP |
BY [s_id]) |
|
|
|
|
|
11 |
UPDATE [subscribers] |
|
|
|
|
|
||
12 |
SET |
[s_name] = |
|
|
|
|
|
|
13 |
|
|
(SELECT CASE WHEN [x] |
> 5 |
|
|
|
|
14 |
|
|
THEN |
(SELECT CONCAT([s_name], |
' [', [x], |
[RED]')) |
||
15 |
|
|
|
'] |
|
|
|
|
|
|
|
|
|
|
|
||
16 |
|
|
WHEN [x] >= 3 AND [x] <= 5 |
|
|
[YELLOW]')) |
||
17 |
|
|
THEN |
(SELECT CONCAT([s_name], |
' [', [x], |
|||
|
|
[GREEN]')) |
||||||
18 |
|
|
|
'] |
|
|
|
|
|
|
|
|
|
|
|
||
19 |
|
|
ELSE (SELECT CONCATl[s_name], ' |
[', [x] '] |
|
|||
20 |
|
|
END |
|
|
|
|
[s id] |
21 |
|
|
FROM |
[prepared_data] |
|
|
|
|
|
|
|
|
|
|
|||
22 |
|
|
WHERE |
[subscribers] [s id] = [prepared data] |
|
|||
|
|
|
|
|
|
|
|
|
Результат работы общего табличного выражения таков:
s_id |
x |
1 |
0 |
2 |
0 |
3 |
3 |
4 |
4 |
Коррелирующий подзапрос в строках 13-22 на основе значения колонки x формирует значение суффикса имени соответствующего читателя. Так получается итоговый результат.
Если есть потребность избавиться от коррелирующего подзапроса в строках 13-22, можно переписать основную часть решения (общее табличное выражение остаётся таким же) с использованием JOIN.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 218/545
Пример 25: использование условий при модификации данных
MS SQL I Решение 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.С |
1 |
UPDATE "subscribers" |
|
2 |
SET |
"s name" = |
3(SELECT
4CASE
5WHEN "x" > 5
6 |
THEN (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" |
19 |
|
GROUP |
BY "s id" "prepared data" |
20 |
WHERE |
"subscribers" "s_id" = "prepared_data" "s_id" |
|
Ещё одно ограничение Oracle проявляется в том, что здесь функция CONCAT
может принимать только два параметра (а нам нужно передать четыре), но мы можем обойти это ограничение с использованием оператора конкатенации строк ||.
Ранее было отмечено, что решение для Oracle является самым универсальным и может быть легко перенесено в другие СУБД. Продемонстрируем это.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 219/545