Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL Решение 2.2.9.b
1-- Вариант 1: использование подзапроса и сортировки
2SELECT DISTINCT [s_id],
3 |
|
|
[s_name], |
4 |
|
|
DATEDIFF(day, [sb_start], [sb_finish]) AS [days] |
5 |
|
FROM |
[subscribers] |
|
|
|
|
6JOIN [subscriptions]
7ON [s_id] = [sb_subscriber]
8WHERE [sb_is_active] = 'N'
9AND DATEDIFF(day, [sb_start], [sb_finish]) =
10(SELECT TOP 1 DATEDIFF(day, [sb_start], [sb_finish]) AS [days]
11 |
|
FROM |
[subscriptions] |
12 |
|
WHERE |
[sb_is_active] = 'N' |
13 |
|
ORDER BY |
[days] ASC) |
14 |
|
|
|
|
|
|
|
1-- Вариант 2: использование общего табличного выражения и функции MIN
2WITH [prepared_data]
3AS (SELECT DISTINCT [s_id],
4 |
|
|
|
[s_name], |
5 |
|
|
|
DATEDIFF(day, [sb_start], [sb_finish]) AS [days] |
6 |
|
FROM |
[subscribers] |
|
7 |
|
|
JOIN |
[subscriptions] |
8 |
|
|
ON |
[s_id] = [sb_subscriber] |
9WHERE [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 |
|
|
DATEDIFF(day, [sb_start], [sb_finish]) AS [days], |
6 |
|
|
RANK() |
7 |
|
|
OVER ( |
8 |
|
|
ORDER BY |
9 |
|
|
DATEDIFF(day, [sb_start], [sb_finish]) ASC |
10 |
|
|
) AS [rank] |
11 |
|
FROM |
[subscribers] |
12 |
|
|
JOIN [subscriptions] |
13 |
|
|
ON [s_id] = [sb_subscriber] |
14WHERE [sb_is_active] = 'N')
15SELECT [s_id],
16[s_name],
17[days]
18FROM [prepared_data]
19WHERE [rank] = 1
Вварианте 2 общее табличное выражение (строки 2-9) возвращает тот же набор данных, что и основная часть запроса в варианте 1 (строки 2-8):
s_id |
s_name |
days |
1 |
Иванов И.И. |
31 |
1 |
Иванов И.И. |
61 |
4 |
Сидоров С.С. |
61 |
Подзапрос в строках 14-15 определяет минимальное значение столбца days, которое затем используется как условие ограничения выборки в основной части запроса (строки 10-14).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 135/545
Пример 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" |
6JOIN "subscriptions"
7ON "s_id" = "sb_subscriber"
8WHERE "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" |
9WHERE "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"
16 |
|
FROM |
"prepared_data" |
17 |
|
WHERE |
"rank" = 1 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 136/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Решение 2.2.9.c{132}.
Здесь мы не будем делать допущений, подобных сделанным в решении задачи 2.2.9.a{132}, и построим запрос исключительно на информации, относящейся к предметной области.
Итак, нам надо по каждому читателю:
•выяснить дату первого визита читателя в библиотеку;
•получить набор взятых им в тот день книг;
•представить набор этих книг в виде строки.
MySQL Решение 2.2.9.c
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 Стр: 137/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Следующий по уровню вложенности подзапрос (строки 5-24) подготавливает данные с именами читателей и названиями книг:
s_id |
s_name |
b_name |
1 |
Иванов И.И. |
Евгений Онегин |
1 |
Иванов И.И. |
Основание и империя |
3 |
Сидоров С.С. |
Основание и империя |
4 |
Сидоров С.С. |
Язык программирования С++ |
И, наконец, основная часть запроса (строки 1-5 и 25) превращает отдельные строки с информацией о книгах читателя (отмечены выше серым фоном) в одну строку с перечнем книг. Так получается итоговый результат.
MS SQL Решение 2.2.9.c
1WITH [step_1]
2AS (SELECT [sb_subscriber],
3 |
|
|
MIN([sb_start]) |
AS [min_date] |
|
|
|
|
|
4 |
|
FROM |
[subscriptions] |
|
5 |
|
GROUP |
BY [sb_subscriber]), |
|
6[step_2]
7AS (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]
15AS (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]) |
23SELECT [s_id],
24[s_name],
25STUFF
26 |
|
((SELECT |
', ' + [int].[b_name] |
27 |
|
FROM |
[step_3] AS [int] |
28 |
|
WHERE |
[ext].[s_id] = [int].[s_id] |
29ORDER BY [int].[b_name]
30FOR xml path(''), type).value('.', 'nvarchar(max)'),
311, 2, '') AS [books_list]
32 FROM [step_3] AS [ext]
33GROUP 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 Стр: 138/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 Стр: 139/545