Gt; 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 строк. В итоге по_

192 Глава 9. Подзапросы

лучаем запрос, где выполняется группировка только необработанных данных таблицы account, а затем результат приукрашивается с помо_ щью данных из трех других таблиц. И все это без всяких соединений.

Как отмечалось ранее, скалярные подзапросы тоже могут появляться в блоке order by. Следующий запрос извлекает данные сотрудников, отсортированные по фамилиям начальников сотрудников и затем по фамилиям самих сотрудников:

mysql> SELECT emp.emp_id, CONCAT(emp.fname, '	', emp.lname) emp_name,
	_>	(SELECT CONCAT(boss.fname, ' ', boss.lname)
	_>	FROM employee boss
	_>	WHERE boss.emp_id = emp.superior_emp_id) boss_name
	_> FROM employee emp
	_> WHERE emp.superior_emp_id IS	NOT NULL
	_> ORDER BY (SELECT boss.lname FROM employee boss
	_>	WHERE boss.emp_id = emp.superior_emp_id), emp.lname;
+________+__________________+_________________+
| emp_id | emp_name	| boss_name	|
+________+__________________+_________________+
|		| Cindy Mason	| John Blake	|
|		| Frank Portman	| John Blake	|
|		| Jane Grossman	| Helen	Fleming	|
|		| Sarah Parker	| Helen	Fleming	|
|		| Chris Tucker	| Helen	Fleming	|
|		| John Blake	| Susan	Hawthorne	|
|		| Helen Fleming	| Susan	Hawthorne	|
|		| John Gooding	| Susan	Hawthorne	|
|		| Theresa Markham	| Susan	Hawthorne	|
|		| Paula Roberts	| Susan Hawthorne	|
|		| Beth Fowler	| Theresa Markham	|
|		| Rick Tulman	| Theresa Markham	|
|		| Samantha Jameson	| Paula	Roberts	|
|		| Thomas Ziegler	| Paula	Roberts	|
|		| Susan Barker	| Michael Smith	|
|		| Robert Tyler	| Michael Smith	|
|		| Susan Hawthorne	| Robert Tyler	|

+________+__________________+_________________+ 17 rows in set (0.01 sec)

Этот запрос использует два связанных скалярных подзапроса: один в блоке select, извлекающий полное имя руководителя каждого со_ трудника, а другой в блоке order by, возвращающий только фамилию руководителя сотрудника для целей сортировки.

Наряду с применением скалярных подзапросов в выражении select можно использовать несвязанные скалярные подзапросы, формирую_ щие значения для выражения insert. Например, предполагается соз_ дать новую строку счета. Предоставлены следующие данные:

• Тип счета («savings account»)

• Федеральный ID клиента («555_55_5555»)

Краткий обзор подзапросов

• Название отделения, в котором был открыт счет («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.

Дата добавления: 2015-08-17