alan_beaulieu-learning_sql-ru (865932), страница 34
Текст из файла (страница 34)
NH Branch | Theresa Markham |67852.33 || savings account| So. NH Branch | Theresa Markham |387.99 |+++++11 rows in set (0.00 sec)Я понимаю, что «на вкус и цвет товарищей нет», но этот вариант за проса мне нравится намного больше, чем большая плоская версия.И он может быстрее выполняться, потому что группировка проведенапо небольшим столбцам внешних ключей (product_cd, open_branch_id,open_emp_id), а не по столбцам, предположительно содержащим длин ные строки (branch.name, product.name, employee.fname, employee.lname).Подзапросы в условиях фильтрацииВо многих примерах данной главы подзапросы используются как вы ражения в условиях фильтрации, поэтому для вас не будет сюрпри зом, что это одно из основных применений подзапросов. Но условияфильтрации, использующие подзапросы, встречаются не только в бло ке 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 находит максимальное число счетов, откры тых одним сотрудником, а основной запрос находит сотрудника, от крывшего это количество счетов.
Если бы с наибольшим числом от 190Глава 9. Подзапросыкрытых счетов были связаны несколько сотрудников, запрос возвра тил бы несколько строк.Подзапросы как генераторы выраженийВ этом последнем разделе главы я завершу тему, с которой начал, –скалярные подзапросы, возвращающие один столбец и одну строку.Кроме условий фильтрации скалярные подзапросы применимы везде,где может появляться выражение, включая блоки select и order by за проса и блок values (значения) выражения insert.Ранее в этой главе, в разделе «Подзапросы, ориентированные на зада чи», было показано, как с помощью подзапроса отделить механизмгруппировки от остального запроса.
Вот вариант того же запроса, ис пользующий подзапросы с той же целью, но по другому:mysql> 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;+++++| product| branch| name| tot_deposits |+++++| NULL| Woburn Branch | Paula Roberts |9345.55 || NULL| So. NH Branch | Theresa Markham |0.00 || 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 || NULL| Quincy Branch | John Blake|50000.00 |+++++14 rows in set (0.01 sec)Между этим запросом и приведенной ранее версией, использующейподзапрос в блоке from, есть два основных различия:• Вместо соединения таблиц product, branch и employee с данными сче та в блоке select используются связанные скалярные подзапросыдля поиска типа счета, отделения и сотрудника.Использование подзапросов•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 строк. В итоге по 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|++++|14 | Cindy Mason| John Blake||15 | Frank Portman| John Blake||9 | Jane Grossman| Helen Fleming ||8 | Sarah Parker| Helen Fleming ||7 | Chris Tucker| Helen Fleming ||13 | John Blake| Susan Hawthorne ||6 | Helen Fleming| Susan Hawthorne ||5 | John Gooding| Susan Hawthorne ||16 | Theresa Markham | Susan Hawthorne ||10 | Paula Roberts| Susan Hawthorne ||17 | Beth Fowler| Theresa Markham ||18 | Rick Tulman| Theresa Markham ||12 | Samantha Jameson | Paula Roberts ||11 | Thomas Ziegler | Paula Roberts ||2 | Susan Barker| Michael Smith ||3 | Robert Tyler| Michael Smith ||4 | Susan Hawthorne | Robert Tyler|++++17 rows in set (0.01 sec)Этот запрос использует два связанных скалярных подзапроса: одинв блоке select, извлекающий полное имя руководителя каждого со трудника, а другой в блоке order by, возвращающий только фамилиюруководителя сотрудника для целей сортировки.Наряду с применением скалярных подзапросов в выражении selectможно использовать несвязанные скалярные подзапросы, формирую щие значения для выражения insert.
Например, предполагается соз дать новую строку счета. Предоставлены следующие данные:• Тип счета («savings account»)• Федеральный ID клиента («555 55 5555»)Краткий обзор подзапросов•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 = '555555555'),'20050125', '20050125', '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 запросов.Очевидно, подзапросы – очень многогранный инструмент, поэтому неотчаивайтесь, если при первом прочтении главы вы поймете не всепредставленные здесь принципы.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
