Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.g: показать информацию по всем пустым комнатам и свободным компьютерам.

Ожидаемый результат 2.2.10.g.

r_id

r_name

c_id

c_room

c_name

3

Пустая комната 1

NULL

NULL

NULL

4

Пустая комната 2

NULL

NULL

NULL

5

Пустая комната 3

NULL

NULL

NULL

NULL

NULL

6

NULL

Свободный компьютер A

NULL

NULL

7

NULL

Свободный компьютер B

NULL

NULL

8

NULL

Свободный компьютер C

Решение 2.2.10.g: используем полное внешнее объединение с исключением. Эта задача является комбинацией задач 2.2.10.c{154} и 2.2.10.e{156}: нужно показать все записи из таблицы rooms, для которых нет соответствия в таблице computers, а также все записи из таблицы computers, для которых нет соответствия в таблице rooms.

С MySQL здесь та же проблема, что и в предыдущей задаче — отсутствие поддержки полного внешнего объединения, что вынуждает нас опять использовать два отдельных запроса, результаты которых объединяются с помощью UNION:

MySQL Решение 2.2.10.g

1SELECT `r_id`,

2`r_name`,

3`c_id`,

4`c_room`,

5`c_name`

6

 

FROM `rooms`

 

7

 

LEFT JOIN

`computers`

8

 

ON

`r_id` = `c_room`

9WHERE `c_id` IS NULL

10UNION

11SELECT `r_id`,

12`r_name`,

13`c_id`,

14`c_room`,

15`c_name`

16

 

FROM

`rooms`

 

17

 

 

RIGHT JOIN

`computers`

18

 

 

ON

`r_id` = `c_room`

 

 

 

 

 

19WHERE `r_id` IS NULL

ВMS SQL Server и Oracle всё проще: достаточно указать, в каких полях мы ожидаем наличие NULL.

MS SQL Решение 2.2.10.g

1SELECT [r_id],

2[r_name],

3[c_id],

4[c_room],

5[c_name]

6

 

FROM

[rooms]

 

 

7

 

 

FULL JOIN

[computers]

8

 

 

ON

[r_id] = [c_room]

9

 

WHERE

[r_id]

IS

NULL

10

 

 

OR [c_id]

IS

NULL

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 160/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Oracle Решение 2.2.10.g

1SELECT "r_id",

2"r_name",

3"c_id",

4"c_room",

5"c_name"

6

 

FROM

"rooms"

 

 

7

 

 

FULL JOIN

"computers"

8

 

 

ON

"r_id" = "c_room"

9

 

WHERE

"r_id"

IS

NULL

10

 

 

OR "c_id"

IS

NULL

Условия в строках 9-10 запросов для MS SQL Server и Oracle не допускают попадания в конечную выборку строк, отличных от имеющих NULL-значение в полях r_id или c_id. Эти поля выбраны не случайно: они являются первичными ключами своих таблиц, и потому появление в них NULL-значения, изначально записанного в таблицу крайне маловероятно в отличие от «обычных полей», где NULL вполне может храниться как признак отсутствия значения.

Графически набор попадающих в конечную выборку записей выглядит так (отмечено серым фоном):

r_name

r_id

c_room

c_id

c_name

Комната с двумя компьютерами

1

1

1

Компьютер A в комнате 1

Комната с двумя компьютерами

1

1

2

Компьютер B в комнате 1

Комната с тремя компьютерами

2

2

3

Компьютер A в комнате 2

Комната с тремя компьютерами

2

2

4

Компьютер B в комнате 2

Комната с тремя компьютерами

2

2

5

Компьютер C в комнате 2

Пустая комната 1

3

NULL

NULL

NULL

Пустая комната 2

4

NULL

NULL

NULL

Пустая комната 3

5

NULL

NULL

NULL

NULL

NULL

NULL

6

Свободный компьютер A

NULL

NULL

NULL

7

Свободный компьютер B

NULL

NULL

NULL

8

Свободный компьютер C

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 161/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.h: показать возможные варианты расстановки компьютеров по комнатам (не учитывать вместимость комнат).

Ожидаемый результат 2.2.10.h.

r_id

r_name

c_id

c_room

c_name

1

Комната с двумя компьютерами

1

1

Компьютер A в комнате 1

2

Комната с тремя компьютерами

1

1

Компьютер A в комнате 1

3

Пустая комната 1

1

1

Компьютер A в комнате 1

4

Пустая комната 2

1

1

Компьютер A в комнате 1

5

Пустая комната 3

1

1

Компьютер A в комнате 1

1

Комната с двумя компьютерами

2

1

Компьютер B в комнате 1

2

Комната с тремя компьютерами

2

1

Компьютер B в комнате 1

3

Пустая комната 1

2

1

Компьютер B в комнате 1

4

Пустая комната 2

2

1

Компьютер B в комнате 1

5

Пустая комната 3

2

1

Компьютер B в комнате 1

1

Комната с двумя компьютерами

3

2

Компьютер A в комнате 2

2

Комната с тремя компьютерами

3

2

Компьютер A в комнате 2

3

Пустая комната 1

3

2

Компьютер A в комнате 2

4

Пустая комната 2

3

2

Компьютер A в комнате 2

5

Пустая комната 3

3

2

Компьютер A в комнате 2

1

Комната с двумя компьютерами

4

2

Компьютер B в комнате 2

2

Комната с тремя компьютерами

4

2

Компьютер B в комнате 2

3

Пустая комната 1

4

2

Компьютер B в комнате 2

4

Пустая комната 2

4

2

Компьютер B в комнате 2

5

Пустая комната 3

4

2

Компьютер B в комнате 2

1

Комната с двумя компьютерами

5

2

Компьютер C в комнате 2

2

Комната с тремя компьютерами

5

2

Компьютер C в комнате 2

3

Пустая комната 1

5

2

Компьютер C в комнате 2

4

Пустая комната 2

5

2

Компьютер C в комнате 2

5

Пустая комната 3

5

2

Компьютер C в комнате 2

1

Комната с двумя компьютерами

6

NULL

Свободный компьютер A

2

Комната с тремя компьютерами

6

NULL

Свободный компьютер A

3

Пустая комната 1

6

NULL

Свободный компьютер A

4

Пустая комната 2

6

NULL

Свободный компьютер A

5

Пустая комната 3

6

NULL

Свободный компьютер A

1

Комната с двумя компьютерами

7

NULL

Свободный компьютер B

2

Комната с тремя компьютерами

7

NULL

Свободный компьютер B

3

Пустая комната 1

7

NULL

Свободный компьютер B

4

Пустая комната 2

7

NULL

Свободный компьютер B

5

Пустая комната 3

7

NULL

Свободный компьютер B

1

Комната с двумя компьютерами

8

NULL

Свободный компьютер C

2

Комната с тремя компьютерами

8

NULL

Свободный компьютер C

3

Пустая комната 1

8

NULL

Свободный компьютер C

4

Пустая комната 2

8

NULL

Свободный компьютер C

5

Пустая комната 3

8

NULL

Свободный компьютер C

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 162/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Решение 2.2.10.h: используем перекрёстное объединение (декартово произведение).

MySQL Решение 2.2.10.h

1-- Вариант 1: без ключевого слова JOIN

2SELECT `r_id`,

3`r_name`,

4`c_id`,

5`c_room`,

6`c_name`

7

 

FROM

`rooms`,

8

 

 

`computers`

 

 

 

 

1-- Вариант 2: с ключевым словом JOIN

2SELECT `r_id`,

3`r_name`,

4`c_id`,

5`c_room`,

6`c_name`

7

 

FROM `rooms`

8

 

CROSS JOIN `computers`

MS SQL Решение 2.2.10.h

1-- Вариант 1: без ключевого слова JOIN

2SELECT [r_id],

3[r_name],

4[c_id],

5[c_room],

6[c_name]

7

 

FROM

[rooms],

8

 

 

[computers]

1-- Вариант 2: с ключевым словом JOIN

2SELECT [r_id],

3[r_name],

4[c_id],

5[c_room],

6[c_name]

7

 

FROM [rooms]

8

 

CROSS JOIN [computers]

Oracle Решение 2.2.10.h

1-- Вариант 1: без ключевого слова JOIN

2SELECT "r_id",

3"r_name",

4"c_id",

5"c_room",

6"c_name"

7

 

FROM

"rooms",

8

 

 

"computers"

 

 

 

 

1-- Вариант 2: с ключевым словом JOIN

2SELECT "r_id",

3"r_name",

4"c_id",

5"c_room",

6"c_name"

7

 

FROM "rooms"

8

 

CROSS JOIN "computers"

 

 

 

При выполнении перекрёстного объединения (декартового произведения) СУБД каждой записи из левой таблицы ставит в соответствие все записи из правой таблицы. Иными словами, СУБД находит все возможные попарные комбинации записей из обеих таблиц.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 163/545

Пример 20: все разновидности запросов на объединение в трёх СУБД

Задача 2.2.10.i: показать возможные варианты перестановки компьютеров по комнатам (компьютер не должен оказаться в той комнате, в которой он сейчас стоит, не учитывать вместимость комнат).

Ожидаемый результат 2.2.10.i.

r_id

r_name

c_id

c_room

c_name

2

Комната с тремя компьютерами

1

1

Компьютер A в комнате 1

3

Пустая комната 1

1

1

Компьютер A в комнате 1

4

Пустая комната 2

1

1

Компьютер A в комнате 1

5

Пустая комната 3

1

1

Компьютер A в комнате 1

2

Комната с тремя компьютерами

2

1

Компьютер B в комнате 1

3

Пустая комната 1

2

1

Компьютер B в комнате 1

4

Пустая комната 2

2

1

Компьютер B в комнате 1

5

Пустая комната 3

2

1

Компьютер B в комнате 1

1

Комната с двумя компьютерами

3

2

Компьютер A в комнате 2

3

Пустая комната 1

3

2

Компьютер A в комнате 2

4

Пустая комната 2

3

2

Компьютер A в комнате 2

5

Пустая комната 3

3

2

Компьютер A в комнате 2

1

Комната с двумя компьютерами

4

2

Компьютер B в комнате 2

3

Пустая комната 1

4

2

Компьютер B в комнате 2

4

Пустая комната 2

4

2

Компьютер B в комнате 2

5

Пустая комната 3

4

2

Компьютер B в комнате 2

1

Комната с двумя компьютерами

5

2

Компьютер C в комнате 2

3

Пустая комната 1

5

2

Компьютер C в комнате 2

4

Пустая комната 2

5

2

Компьютер C в комнате 2

5

Пустая комната 3

5

2

Компьютер C в комнате 2

Решение 2.2.10.i: используем перекрёстное объединение (декартово произведение) с исключением.

MySQL Решение 2.2.10.i

1-- Вариант 1: без ключевого слова JOIN

2SELECT `r_id`,

3`r_name`,

4`c_id`,

5`c_room`,

6`c_name`

7

 

FROM

`rooms`,

8

 

 

`computers`

9

 

WHERE

`r_id` != `c_room`

1

 

-- Вариант 2: с ключевым словом JOIN

2

 

SELECT

`r_id`,

3

 

 

`r_name`,

4

 

 

`c_id`,

5

 

 

`c_room`,

6

 

 

`c_name`

7

 

FROM

`rooms`

8CROSS JOIN `computers`

9WHERE `r_id` != `c_room`

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 164/545

Источник: https://studfile.net/preview/16418462/

Смотрите также:

02_ЛР
05. Частная патология ССС - Инфаркт миокарда
07. ПА_Лекция 7_Патология энд.жел
1. Охарактеризуйте роль Банка России как кредитора последней инстанции
1. Понятие регулярности полетов ла факторы, влияющие на регулярность вылета
1. Строение механизмов
107-112
11 занятие бх
1131
1230