изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	21 из 31

1 ... 17 18 19 20 21 22 23 24 ... 31

Подзапросы в условиях фильтрации
Во многих примерах данной главы подзапросы используются как вы ражения в условиях фильтрации, поэтому для вас не будет сюрпри зом, что это одно из основных применений подзапросов. Но условия фильтрации, использующие подзапросы, встречаются не только в бло ке where. Например, следующий запрос использует блок having для по иска сотрудника, открывшего наибольшее количество счетов:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> HAVING COUNT(*) = (SELECT MAX(emp_cnt.how_many)
> FROM (SELECT COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id) emp_cnt);
+
+
+
| open_emp_id | how_many |
+
+
+
| 1 | 8 |
+
+
+
1 row in set (0.01 sec)
Подзапрос блока having находит максимальное число счетов, откры тых одним сотрудником, а основной запрос находит сотрудника, от крывшего это количество счетов. Если бы с наибольшим числом от

Использование подзапросов
191
•
Результирующий набор содержит 14 строк, а не 11, и три типа сче тов – null.
Три дополнительные строки появляются в результирующем наборе потому, что предыдущая версия запроса включала условие фильтра ции p.product_type_cd = 'ACCOUNT'. Этот фильтр исключал строки для счетов типов INSURANCE (страховка) и LOAN (ссуда), например не большие ссуды коммерческим предприятиям. Поскольку в этой вер сии запроса нет соединения с таблицей product, нет возможности вклю чить условие фильтрации в основной запрос. Связанный подзапрос к таблице product включает этот фильтр, но единственный производи мый им эффект – указание null вместо типа счета. Если хотите изба виться от дополнительных трех строк, можно соединить таблицу product с таблицей account и включить условие фильтрации или просто сделать следующее:
mysql> SELECT all_prods.product, all_prods.branch,
> all_prods.name, all_prods.tot_deposits
> FROM
> (SELECT
> (SELECT p.name FROM product p
> WHERE p.product_cd = a.product_cd
> AND p.product_type_cd = 'ACCOUNT') product,
> (SELECT b.name FROM branch b
> WHERE b.branch_id = a.open_branch_id) branch,
> (SELECT CONCAT(e.fname, ' ', e.lname) FROM employee e
> WHERE e.emp_id = a.open_emp_id) name,
> SUM(a.avail_balance) tot_deposits
> FROM account a
> GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id) all_prods
> WHERE all_prods.product IS NOT NULL;
+
+
+
+
+
| product | branch | name | tot_deposits |
+
+
+
+
+
| certificate of deposit | Headquarters | Michael Smith | 11500.00 |
| certificate of deposit | Woburn Branch | Paula Roberts | 8000.00 |
| checking account | Headquarters | Michael Smith | 782.16 |
| checking account | Woburn Branch | Paula Roberts | 3315.77 |
| checking account | Quincy Branch | John Blake | 1057.75 |
| checking account | So. NH Branch | Theresa Markham | 67852.33 |
| money market account | Headquarters | Michael Smith | 14832.64 |
| money market account | Quincy Branch | John Blake | 2212.50 |
| savings account | Headquarters | Michael Smith | 767.77 |
| savings account | Woburn Branch | Paula Roberts | 700.00 |
| savings account | So. NH Branch | Theresa Markham | 387.99 |
+
+
+
+
+
11 rows in set (0.01 sec)
Теперь, после помещения предыдущего запроса в подзапрос (названный all_prods
) и добавления условия фильтрации для исключения значений null столбца product, запрос возвращает желаемые 11 строк. В итоге по

Краткий обзор подзапросов
193
•
Название отделения, в котором был открыт счет («Quincy Branch»)
•
Имя и фамилия операциониста, открывшего счет («Frank Portman»)
Прежде чем можно будет создать строку в таблице account, понадобит ся найти значения ключей всех этих элементов данных, чтобы запол нить столбцы внешних ключей таблицы account. Сделать это можно двумя способами: выполнить четыре запроса для извлечения значений первичных ключей и поместить эти значения в выражение insert или получить значения четырех ключей с помощью подзапросов внутри выражения insert. Вот пример второго подхода:
INSERT INTO account
(account_id, product_cd, cust_id, open_date, last_activity_date,
status, open_branch_id, open_emp_id, avail_balance, pending_balance)
VALUES (NULL,
(SELECT product_cd FROM product WHERE name = 'savings account'),
(SELECT cust_id FROM customer WHERE fed_id = '555 55 5555'),
'2005 01 25', '2005 01 25', 'ACTIVE',
(SELECT branch_id FROM branch WHERE name = 'Quincy Branch'),
(SELECT emp_id FROM employee WHERE lname = 'Portman' AND fname = 'Frank'),
0, 0);
Единственное SQL выражение позволяет вам одновременно создать строку в таблице account и найти четыре значения столбцов внешнего ключа. Однако у этого подхода есть один недостаток. Если с помощью подзапросов заполнять столбцы, допускающие значения null, выраже ние выполнится успешно, даже если один из подзапросов не возвратит значение. Например, если в четвертом подзапросе в имени Frank Port man сделана опечатка, строка в таблице account будет все равно созда на, но столбцу open_emp_id будет присвоено значение null.
Краткий обзор подзапросов
В этой главе рассмотрено множество тем, поэтому, пожалуй, не лишне вкратце повторить их. Примеры данной главы продемонстрировали подзапросы, которые:
•
Возвращают один столбец и одну строку, один столбец и несколько строк, а также несколько столбцов и строк.
•
Не зависят от содержащего выражения (несвязанные подзапросы).
•
Используют один или более столбцов из содержащего выражения
(связанные подзапросы).
•
Применяются в условиях, используемых операторами сравнения и специальными операторами in, not in, exists и not exists.
•
Могут находиться в выражениях select, update, delete и insert.
•
Формируют таблицы, которые можно соединить в запросе с други ми таблицами.

194
Глава 9. Подзапросы
•
Позволяют формировать значения для заполнения таблицы или столбцов результирующего набора запроса.
•
Используются в блоках select, from, where, having и order by запросов.
Очевидно, подзапросы – очень многогранный инструмент, поэтому не отчаивайтесь, если при первом прочтении главы вы поймете не все представленные здесь принципы. Продолжайте экспериментировать с подзапросами и вскоре обнаружите, что при написании каждого не обычного SQL выражения очень полезно рассматривать возможность использования подзапроса.
Упражнения
Эти упражнения ориентированы на проверку понимания подзапросов.
Решения приведены в приложении С.
9.1
Создайте запрос к таблице account, использующий условие фильтра ции с несвязанным подзапросом к таблице product для поиска всех кре дитных счетов (product.product_type_cd = 'LOAN'). Должны быть выбра ны ID счета, код счета, ID клиента и доступный остаток.
9.2
Переработайте запрос из упражнения 9.1, используя связанный подза прос к таблице product для получения того же результата.
9.3
Соедините следующий запрос с таблицей employee, чтобы показать уро вень квалификации каждого сотрудника:
SELECT 'trainee' name, '2004 01 01' start_dt, '2005 12 31' end_dt
UNION ALL
SELECT 'worker' name, '2002 01 01' start_dt, '2003 12 31' end_dt
UNION ALL
SELECT 'mentor' name, '2000 01 01' start_dt, '2001 12 31' end_dt
Дайте подзапросу псевдоним levels (уровни) и включите ID сотрудни ка, имя, фамилию и квалификацию (levels.name). (Совет: в условии со единения определяйте диапазон, в который попадает столбец em ployee.start_date
, с помощью условия неравенства.)
9.4
Создайте запрос к таблице employee для получения ID, имени и фами лии сотрудника вместе с названиями отдела и отделения, к которым он приписан. Не используйте соединение таблиц.

И снова соединения
На данный момент читатель должен владеть концепцией внутреннего соединения, представленной в главе 5. Основное внимание в этой главе уделено другим способам соединения таблиц, включая внешнее и пере крестное соединения.
Внешние соединения
До сих пор ни в одном из приведенных примеров, включающих запросы к нескольким таблицам, не поднимался вопрос о том, что не все строки таблицы могут соответствовать условиям соединения. Например, при соединении таблицы account с таблицей customer ничего не было сказа но о возможности отсутствия для значения столбца cust_id таблицы account соответствующего значения в столбце cust_id таблицы customer.
Если бы такое случилось, некоторые строки одной из таблиц не вошли бы в результирующий набор.
На всякий случай давайте проверим данные таблицы. Вот столбцы ac count_id и cust_id таблицы account:
mysql> SELECT account_id, cust_id
> FROM account;
+
+
+
| account_id | cust_id |
+
+
+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
| 8 | 4 |
| 9 | 4 |

196
Глава 10. И снова соединения
| 10 | 4 |
| 11 | 5 |
| 12 | 6 |
| 13 | 6 |
| 14 | 7 |
| 15 | 8 |
| 16 | 8 |
| 17 | 9 |
| 18 | 9 |
| 19 | 9 |
| 20 | 10 |
| 21 | 10 |
| 22 | 11 |
| 23 | 12 |
| 24 | 13 |
+
+
+
24 rows in set (0.04 sec)
Имеется 24 счета 13 разных клиентов с ID клиента от 1 до 13, по край ней мере по одному счету на каждого. Вот множество клиентских ID
таблицы customer:
mysql> SELECT cust_id
> FROM customer;
+
+
| cust_id |
+
+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+
+
13 rows in set (0.02 sec)
В таблице customer 13 строк с ID от 1 до 13. Таким образом, каждый ID
клиента включен в таблицу account, по крайней мере, один раз. Следо вательно, при соединении двух таблиц по столбцу cust_id можно ожи дать, что в результирующий набор будут включены все 24 строки (если нет других условий фильтрации):
mysql> SELECT a.account_id, c.cust_id
> FROM account a INNER JOIN customer c
> ON a.cust_id = c.cust_id;

Внешние соединения
197
+
+
+
| account_id | cust_id |
+
+
+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
| 8 | 4 |
| 9 | 4 |
| 10 | 4 |
| 11 | 5 |
| 12 | 6 |
| 13 | 6 |
| 14 | 7 |
| 15 | 8 |
| 16 | 8 |
| 17 | 9 |
| 18 | 9 |
| 19 | 9 |
| 20 | 10 |
| 21 | 10 |
| 22 | 11 |
| 23 | 12 |
| 24 | 13 |
+
+
+
24 rows in set (0.00 sec)
Как и ожидалось, в результирующем наборе представлены все 24 сче та. Но что произойдет, если соединить таблицу account с одной из спе циализированных таблиц клиентов, например таблицей business?
mysql> SELECT a.account_id, b.cust_id, b.name
> FROM account a INNER JOIN business b
> ON a.cust_id = b.cust_id;
+
+
+
+
| account_id | cust_id | name |
+
+
+
+
| 20 | 10 | Chilton Engineering |
| 21 | 10 | Chilton Engineering |
| 22 | 11 | Northeast Cooling Inc. |
| 23 | 12 | Superior Auto Body |
| 24 | 13 | AAA Insurance Inc. |
+
+
+
+
5 rows in set (0.00 sec)
Теперь в результирующем наборе только пять строк вместо 24. Загля нем в таблицу business, чтобы понять, почему так произошло:
mysql> SELECT cust_id, name
> FROM business;

198
Глава 10. И снова соединения
+
+
+
| cust_id | name |
+
+
+
| 10 | Chilton Engineering |
| 11 | Northeast Cooling Inc. |
| 12 | Superior Auto Body |
| 13 | AAA Insurance Inc. |
+
+
+
4 rows in set (0.01 sec)
Из 13 строк таблицы клиентов только четыре относятся к юридиче ским лицам. И поскольку у одного из юридических лиц два счета, в об щей сложности с юридическими лицами связаны пять строк таблицы account
Но что делать, если требуется, чтобы запрос возвращал все счета, но при этом включал название фирмы, только если счет связан с юриди ческим лицом? Это пример, когда необходимо внешнее соединение
(outer join) таблиц account и business:
mysql> SELECT a.account_id, a.cust_id, b.name
> FROM account a LEFT OUTER JOIN business b
> ON a.cust_id = b.cust_id;
+
+
+
+
| account_id | cust_id | name |
+
+
+
+
| 1 | 1 | NULL |
| 2 | 1 | NULL |
| 3 | 1 | NULL |
| 4 | 2 | NULL |
| 5 | 2 | NULL |
| 6 | 3 | NULL |
| 7 | 3 | NULL |
| 8 | 4 | NULL |
| 9 | 4 | NULL |
| 10 | 4 | NULL |
| 11 | 5 | NULL |
| 12 | 6 | NULL |
| 13 | 6 | NULL |
| 14 | 7 | NULL |
| 15 | 8 | NULL |
| 16 | 8 | NULL |
| 17 | 9 | NULL |
| 18 | 9 | NULL |
| 19 | 9 | NULL |
| 20 | 10 | Chilton Engineering |
| 21 | 10 | Chilton Engineering |
| 22 | 11 | Northeast Cooling Inc. |
| 23 | 12 | Superior Auto Body |
| 24 | 13 | AAA Insurance Inc. |
+
+
+
+
24 rows in set (0.00 sec)

Внешние соединения

1 ... 17 18 19 20 21 22 23 24 ... 31