Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 10: использование группировки данных
Задание 2.1.10.TSK.A: переписать решение 2.1.10.c так, чтобы при подсчёте возвращённых и невозвращённых книг СУБД оперировала исходными значениями поля sb_is_active (т.е. Y и N), а преобразование в «Returned» и «Not returned» происходило после подсчёта.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 70/545
Выборка из нескольких таблиц
2.2. Выборка из нескольких таблиц
2.2.1.Пример 11: запросы на объединение как способ получения человекочитаемых данных
Следующие две задачи уже были упомянуты ранее{14}, сейчас мы рассмотрим их подробно.
Задача 2.2.1.a{67}: показать всю человекочитаемую информацию обо всех книгах (т.е. название, автора, жанр).
Задача 2.2.1.b{69}: показать всю человекочитаемую информацию обо всех обращениях в библиотеку (т.е. имя читателя, название взятой книги).
Ожидаемыйрезультат2.2..21..1a..b.
|
b bname |
|
|
s_aid_ |
|
names_name |
g namesbstart |
|
sb_finish |
||
|
ЕвгенийОнегин |
А |
.1С. ПушкинИванов ИКлассика.И. |
2011-01-12 |
2011-02-12 |
||||||
|
Сказкао орыбрыбакеи ирыбке |
А |
.1С. ПушкинИванов ИКлассика.И. |
2012-06-11 |
2012-08-11 |
||||||
|
КурсИскусствотеоретическойпрограммфированиязики |
Л |
.1Д. ЛандауИванов ИКлассика.И. |
2014-08-03 |
2014-10-03 |
||||||
|
КурсПсихологиятеоретическойпрограммированияфизики |
Е |
.1М. ЛифшицИванов ИКлассика.И. |
2015-10-07 |
2015-11-07 |
||||||
|
ИскусствоОснованиепрограммированияимперия |
Д |
|
.1Кнут |
|
Иванов ИКлассика.И. |
|
2011-01-12 |
2011-02-12 |
||
ЕвгеОснованиийОнегинимперия |
А |
|
.3С. ПушкинСидоровПоэзияС.С. |
|
2012-05-17 |
2012-07-17 |
|||||
|
СказкаЯзык программрыбаке ированиярыбке С++ |
А |
|
.3С. |
ПушкинСидоровПоэзияС.С. |
|
2014-08-03 |
2014-10-03 |
|||
|
ПсЕвгенийихологияОнегинпрограммирования |
Д |
|
.3КарнегиС доровПрограммированиеС.С. 2014-08-03 |
2014-09-03 |
||||||
|
ПсихологияЯзыкпрограммированияС++ |
Б |
.4СтрауструпСидоровПрограммированиеС.С. 2012-06-11 |
2012-08-11 |
|||||||
|
ЯзыкЕвгенийпрограммОнегирования С++ |
Б |
.4СтрауструпСидоровПрограммированиеС.С. 2015-10-07 |
2015-03-07 |
|||||||
|
ИскусствоПсихолгияпрогрограммирования |
Д |
|
.4Кнут |
|
СидоровПрограммированиеС.С. 2015-10-08 |
2025-11-08 |
||||
Психология программирования |
Д. Карнеги |
Психология |
|
|
|||||||
Психология программирования |
Б. Страуструп |
Психология |
|
|
|||||||
Основание и империя |
А. Азимов |
Фантастика |
|
|
|||||||
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 71/545
Пример 11: запросы на объединение как способ получения человекочитаемых данных
Решение 2.2.1 .a{66}.
MySQL і Решение 2.2.1.a
1SELECT 'b name',
2'a name',
3'g name'
4 FROM 'books'
5JOIN 'm2m books authors' USING('b id')
6JOIN 'authors' USING('a id')
7JOIN 'm2m books genres' USING('b id')
8JOIN 'genres' USING('g_id')
MS SQL |
|
Решение 2.2.1.a | |
|
1 |
SELECT [b name], |
||
2 |
|
|
[a name], |
3 |
|
|
[g name] |
4 |
FROM |
[books] |
|
5 |
|
|
JOIN [m2m books authors] |
6 |
|
|
ON [books] [b id] = [m2m books authors] [b id] |
7 |
|
|
JOIN [authors] |
8 |
|
|
ON [m2m books authors] [a id] = [authors] [a id] |
9 |
|
|
JOIN [m2m books genres] |
10 |
|
|
ON [books] [b id] = [m2m books genres] [b id] |
11 |
|
|
JOIN [genres] |
12 |
|
|
ON [m2m books genres] [g id] = [genres] [g id] |
|
|
|
|
|
Oracl |
і |
Решение 2.2.1.a |
| |
|
e |
|
||||
|
|
|
|
|
|
1 |
|
SELECT "b name" |
|
||
2 |
|
|
|
"a name" |
|
3 |
|
|
|
"g name" |
|
4 |
|
FROM |
"books" |
|
|
5 |
|
|
|
JOIN "m2m books authors" USING("b id") |
|
6 |
|
|
|
JOIN "authors" USING("a id" |
|
7 |
|
|
|
JOIN "m2m books genres" USING "b id") |
|
8 |
|
|
|
JOIN "genres" USING("g_ _id" |
|
Ключевое отличие решений для MySQL и Oracle от решения для MS SQL Server заключается в том, что эти две СУБД поддерживают специальный синтаксис указания полей, по которым необходимо производить объединение: если такие поля имеют одинаковое имя в объединяемых таблицах, вместо конструкции ON
первая_таблица.поле = вторая_таблица.поле можно использовать USING
(поле) , что зачастую очень сильно повышает читаемость запроса.
Несмотря на то, что задача 2.2.1.а является, пожалуй, самым простым случаем использования JOIN, в ней мы объединяем пять таблиц в одном запросе, по-
тому покажем графически на примере одной строки, как формируется финальная выборка.
В рассматриваемых таблицах есть и другие поля, но здесь показаны лишь те, которые представляют интерес в контексте данной задачи.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 72/545
Пример 11: запросы на объединение как способ получения человекочитаемых данных
Первое действие:
[books] JOIN [m2m_books_authors]
ON [books] [b id] = [m2m books authors] [b id]
Таблица books:
b |
Jd |
b_name |
||
_ |
|
Евгений Онегин |
|
|
|
|
////////////////////////////////. |
|
|
|
|
|
|
|
|
Таблица m2m_books_authors: |
|||
|
|
|
|
|
b |
id |
a_id |
|
|
|
|
7 |
|
|
|
|
|
|
|
|
Промежуточный результат: |
|||
|
|
|
|
|
b_id |
b_name |
a_id |
|
|
1 |
|
Евгений Онегин |
7 |
|
Второе действие:
{результат первого действия} JOIN [authors] ON [m2m books authors] [a id] = [authors] [a id]
|
bjd |
bname |
a id |
|
|
|
|
|
|
|
Таблица autetfors: |
|
|
|
|
a_id |
aname |
||
7 |
A.C. Пушкин |
|
|
|
|
Промежуточный результат: |
|||
|
|
|
|
|
|
b_id |
b_name |
a_name |
|
|
1 |
Евгений Онегин |
А.С. Пушкин |
|
Третье действие:
{результат второго действия} JOIN [m2m_books_genres] ON [books] [b id] = [m2m books genres] [b id]
bjd |
b_name |
|
a_name |
|
|||
1 |
|
|
Евгений Онегин |
|
А.С. Пушкин |
|
|
|
|
|
|
|
аблица m2m_books_genres: |
||
|
|
|
|
|
|
|
|
b |
Jd |
|
g_id |
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
..........5 |
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 73/545
Пример 11: запросы на объединение как способ получения человекочитаемых данных
Промежуточный результат:
b_name |
a_name |
g_id |
Евгений Онегин |
А.С. Пушкин |
1 |
Евгений Онегин |
А.С. Пушкин |
5 |
Четвёртое действие:
{результат третьего действия} JOIN [genres] ON [m2m books genres] [g id] = [genres] [g id]
|
b_name |
|
|
a_name |
g_id |
|
|
|
Евгений Онегин |
|
А.С. Пушкин |
1 |
|
||
|
Евгений Онегин |
|
А.С. Пуіу^н |
5 |
|
||
|
Таблица genres: |
|
|
||||
|
|
|
|
|
g_name |
|
|
|
gjd |
|
|
|
|
|
|
|
І ......... |
V / / / |
/ / / / |
|
|
|
|
|
ПОЗЗИЯ |
|
|
|
|
|
|
|
|
|
|
|
|||
|
5 |
Классика |
|
|
|||
|
|
|
|
|
|
|
|
|
Итоговый результат: |
|
|
||||
|
|
|
|
|
|
|
|
|
b_name |
|
|
a_name |
g_name |
||
|
Евгений Онегин |
|
А.С. Пушкин |
Поэзия |
|||
|
Евгений Онегин |
|
А.С. Пушкин |
Классика |
|||
Аналогичная последовательность действий повторяется для каждой строки из таблицы books, что и приводит к ожидаемому результату 2.2.1.а.
'IT Решение 2.2.1. b{66}.
MySQL I Решение 2.2.1.b |
1SELECT 'b name',
2's id',
3's name' ,
4'sb start',
5'sb finish'
6 |
FROM 'books' |
7 |
JOIN 'subscriptions' |
8 |
ON 'b id' = 'sb book' |
9JOIN 'subscribers'
10ON 'sb subscriber' = 's id'
MS SQL |
Решение 2.2.1.b I |
1SELECT [b name]
2[s id],
3[s name],
4[sb start],
5[sb finish]
6 |
FROM |
[books] |
7 |
|
JOIN [subscriptions] |
8 |
|
ON [b id] = [sb book] |
9JOIN [subscribers]
10ON [sb subscriber] = [s id]
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 74/545