Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
В варианте 3 общее табличное выражение в строках 2-14 готовит уже знакомый нам набор данных, но ещё и ранжированный по значению столбца days:
s_id |
s_name |
days |
rank |
1 |
Иванов И.И. |
31 |
1 |
1 |
Иванов И.И. |
61 |
4 |
4 |
Сидоров С.С. |
61 |
4 |
В основной части запроса (строки 15-19) остаётся только извлечь из этих подготовленных результатов строки, «занимающие первое место» (rank = 1).
Решение для Oracle аналогично решению для MS SQL Server.
Oracle і |
Решение 2.2.9.b і |
1-- Вариант 1: использование подзапроса и сортировки
2SELECT DISTINCT "s id",
3 |
|
"s name", |
4 |
|
( "sb finish" - "sb start" ) AS "days" |
5 |
FROM |
"subscribers" |
6 |
|
JOIN "subscriptions" |
7 |
|
ON "s id" = "sb subscriber" |
8 |
WHERE |
"sb is active" = 'N' |
9AND ( "sb finish" - "sb start" ) =
10(SELECT "min days"
11 |
FROM |
(SELECT ("sb finish" - "sb start" ) |
AS "min days", |
|
12 |
|
|
ROW NUMBER() |
|
13 |
|
|
OVER( |
|
14 |
|
|
ORDER BY ( "sb finish" - "sb start") ASC) AS "rn" |
|
15 |
|
FROM |
"subscriptions" |
|
16 |
|
WHERE |
"sb is active" = 'N') |
|
17 |
WHERE |
"rn" = 1 |
|
|
1-- Вариант 2: использование общего табличного выражения и функции MIN
2WITH "prepared data"
3AS (SELECT DISTINCT "s id"
4 |
|
"s name", |
5 |
|
( "sb finish" - "sb start" ) AS "days" |
6 |
FROM |
"subscribers" |
7 |
|
JOIN "subscriptions" |
8 |
|
ON "s id" = "sb subscriber" |
9 |
WHERE |
"sb is active" = 'N') |
10SELECT "s id",
11"s name"
12"days"
13 |
FROM |
"prepared data" |
|
14 |
WHERE |
"days" = (SELECT MIN("days" |
|
15 |
|
FROM |
"prepared_data" |
1-- Вариант 3: использование общего табличного выражения и ранжирования
2WITH "prepared data"
3AS (SELECT DISTINCT "s id"
4 |
|
"s name", |
|
5 |
|
("sb finish" - "sb start") |
AS "days" |
6 |
|
RANK() |
|
7 |
|
OVER ( |
|
8 |
|
ORDER BY "sb finish" - "sb start" |
ASC ) AS "rank" |
9 |
FROM |
"subscribers" |
|
10 |
|
JOIN "subscriptions" |
|
11 |
|
ON "s id" = "sb subscriber" |
|
12WHERE "sb is active" = 'N')
13SELECT "s id",
14"s name"
15"days"
16FROM "prepared data"
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 145/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
17 WHERE "rank" = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 146/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Ч р Решение 2.2.9.c{132}.
Здесь мы не будем делать допущений, подобных сделанным в решении задачи 2.2.9.a{132}, и построим запрос исключительно на информации, относящейся к предметной области.
Итак, нам надо по каждому читателю:
•выяснить дату первого визита читателя в библиотеку;
•получить набор взятых им в тот день книг;
•представить набор этих книг в виде строки.
MySQL Решение 2.2.9.С
1SELECT 's id',
2's name',
3GROUP CONCAT 'b name' ORDER BY 'b name' SEPARATOR ', ')
4AS 'books list'
5 |
FROM (SELECT 's_id', |
||
6 |
|
's |
name', |
7 |
|
'b |
name' |
8 |
FROM |
'subscribers' |
|
9 |
|
JOIN (SELECT 'subscriptions' 'sb subscriber', |
|
10 |
|
'subscriptions' 'sb_book' |
|
11 |
FROM |
'subscriptions' |
|
12 |
|
JOIN (SELECT 'sb subscriber', |
|
13 |
|
|
MIN('sb start') AS 'min date' |
14 |
|
FROM |
'subscriptions' |
15 |
|
GROUP |
BY 'sb subscriber') |
16 |
|
AS 'first visit' |
|
17 |
|
ON 'subscriptions' 'sb subscriber' = |
|
18 |
|
'first visit' 'sb subscriber' |
|
19 |
|
AND 'subscriptions' 'sb start' = |
|
20 |
|
'first visit' 'min date') |
|
21 |
AS 'books list' |
|
|
22 |
ON 's id' = 'sb subscriber' |
||
23 |
JOIN 'books' |
|
|
24 |
ON 'sb book' = 'b id') AS 'prepared data' |
||
25 |
GROUP BY 's id' |
|
|
Наиболее глубоко вложенный подзапрос (строки 12-16) определяет дату первого визита в библиотеку каждого из читателей:
sb_subscriber |
min_date |
1 |
2011-01-12 |
3 |
2012-05-17 |
4 |
2012-06-11 |
Следующий по уровню вложенности подзапрос (строки 9-20) определяет список книг, взятых каждым из читателей в дату своего первого визита в библиотеку:
sb_subscriber |
sb_book |
1 |
1 |
1 |
3 |
3 |
3 |
4 |
5 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 147/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Следующий по уровню вложенности подзапрос (строки 5-24) подготавливает данные с именами читателей и названиями книг:
s_id |
s_name |
b_name |
1 |
Иванов И.И. |
Евгений Онегин |
1 |
Иванов И.И. |
Основание и империя |
3 |
Сидоров С.С. |
Основание и империя |
4 |
Сидоров С.С. |
Язык программирования С++ |
И, наконец, основная часть запроса (строки 1-5 и 25) превращает отдельные строки с информацией о книгах читателя (отмечены выше серым фоном) в одну строку с перечнем книг. Так получается итоговый результат.
MS SQL і Решение 2.2.9.C |
і |
|
1 |
WITH [step 1] |
|
2 |
AS (SELECT [sb subscriber], |
|
3 |
|
MIN [sb start]) AS [min date] |
4 |
FROM |
[subscriptions] |
5 |
GROUP |
BY [sb subscriber]), |
6 |
[step_2] |
|
7 |
AS (SELECT [subscriptions] [sb subscriber], |
|
8 |
|
[subscriptions] [sb book] |
9 |
FROM |
[subscriptions] |
10 |
|
JOIN [step 1] |
11 |
|
ON [subscriptions] [sb subscriber] = |
12 |
|
[step 1] [sb subscriber] |
13 |
|
AND [subscriptions] [sb start] = [step 1] [min date]), |
14 |
[step_3] |
|
15 |
AS (SELECT [s id], |
|
16 |
|
[s name] |
17 |
|
[b name] |
18 |
FROM |
[subscribers] |
19 |
|
JOIN [step 2] |
20 |
|
ON [s id] = [sb subscriber] |
21 |
|
JOIN [books] |
22 |
|
ON [sb book] = [b id] i |
23 |
SELECT [s id], |
|
24 |
[s name] , |
|
25 |
STUFF |
|
26 |
((SELECT |
', ' + [int] [b name] |
27 |
FROM |
[step 3] AS [int] |
28 |
WHERE |
[ext] [s id] = [int] [s id] |
29 |
ORDER |
BY [int] [b name] |
30 |
FOR xml path(''), type) value '.', 'nvarchar(max)'), |
|
31 |
1, 2, |
'') AS [books list] |
32 |
FROM |
AS [ext] |
33 |
GROUP BY [s id] |
[s name] |
В решении для MS SQL Server общие табличные выражения возвращают те же данные, что и подзапросы в решении для MySQL.
Общее табличное выражение step_1 (строки 1-5) определяет дату первого визита в библиотеку каждого из читателей:
sb_subscriber |
min_date |
1 |
2011-01-12 |
3 |
2012-05-17 |
4 |
2012-06-11 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 148/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Общее табличное выражение step_2 (строки 6-13) определяет список книг, взятых каждым из читателей в дату своего первого визита в библиотеку:
sb_subscriber |
sb_book |
1 |
1 |
1 |
3 |
3 |
3 |
4 |
5 |
Общее табличное выражение step_3 (строки 14-22) подготавливает данные с именами читателей и названиями книг:
s_id |
s_name |
b_name |
1 |
Иванов И.И. |
Евгений Онегин |
1 |
Иванов И.И. |
Основание и империя |
3 |
Сидоров С.С. |
Основание и империя |
4 |
Сидоров С.С. |
Язык программирования С++ |
И, наконец, основная часть запроса (строки 23-33) превращает отдельные строки с информацией о книгах читателя (отмечены выше серым фоном) в одну строку с перечнем книг. Так получается итоговый результат.
Решение для Oracle аналогично решению для MS SQL Server.
Стоит лишь отметить, что последний шаг (превращение нескольких строк с названиями книг в одну строку) реализуется совершенно по-разному в каждой из СУБД в силу крайне серьёзных различий в синтаксисе и наборе поддерживаемых функций. Подробнее эта операция рассмотрена в задаче 2.2.2.a{71}.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 149/545