alan_beaulieu-learning_sql-ru (865932), страница 33

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 33)

Вот таблица, сформированная подзапросом:mysql> SELECT dept_id, COUNT(*) how_many> FROM employee> GROUP BY dept_id;+++| dept_id | how_many |+++|1 |14 ||2 |1 ||3 |3 |+++3 rows in set (0.00 sec)Подзапрос назван e_cnt и соединен с таблицей department по столбцуdept_id. После этого основной запрос извлекает ID отдела и имя из таблицы department, а также количество сотрудников из подзапроса e_cnt.Подзапросы, используемые для формирования таблиц, должны бытьнесвязанными; они выполняются первыми, и полученные таблицыподдерживаются в памяти, пока основной запрос завершает выполнение.

Подзапросы предлагают необычайную гибкость при написаниизапросов. С их помощью можно выходить далеко за пределы наборадоступных таблиц, создавать практически любое представление необ185Использование подзапросовходимых данных и затем соединять эту таблицу с другими таблицамиили таблицами, сформированными подзапросами. Теперь создать отчет или сформировать набор данных для внешних систем можно с помощью единственного запроса. Раньше для этого требовалось несколько запросов или использование процедурного языка.Формирование таблицС помощью подзапросов можно как резюмировать имеющиеся данные,так и формировать данные, которых в БД нет ни в какой форме.

Например, требуется сгруппировать клиентов по денежным суммам, размещенным на депозитных счетах, но использовать для этого описаниягрупп, не хранящиеся в БД. Скажем, надо разбить клиентов на следующие группы:ГруппаНижний предел(долларов)Верхний предел(долларов)Small Fry (мелкота)04 999,99Average Joes (середняки)5 0009 999,99Heavy Hitters (тяжеловесы)10 0009 999 999,99Чтобы сформировать эти группы в рамках одного запроса, потребуетсяспособ определения этих трех групп. Первый шаг – создать запрос,формирующий описания групп:mysql> SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit> UNION ALL> SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit> UNION ALL> SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit;++++| name| low_limit | high_limit |++++| Small Fry|0 |4999.99 || Average Joes |5000 |9999.99 || Heavy Hitters |10000 | 9999999.99 |++++3 rows in set (0.00 sec)Здесь с помощью оператора для работы с наборами union all (объединить все) результаты трех отдельных запросов сводятся в один результирующий набор.

Каждый запрос получает три литерала. Результатыэтих трех запросов объединяются для формирования таблицы, состоящей из трех строк и трех столбцов. Теперь у нас есть запрос для формирования необходимых групп. Его можно поместить в блок from другогозапроса для формирования групп клиентов:mysql> SELECT groups.name, COUNT(*) num_customers> FROM> (SELECT SUM(a.avail_balance) cust_balance186Глава 9. Подзапросы> FROM account a INNER JOIN product p>ON a.product_cd = p.product_cd> WHERE p.product_type_cd = 'ACCOUNT'> GROUP BY a.cust_id) cust_rollup INNER JOIN> (SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit> UNION ALL> SELECT 'Average Joes' name, 5000 low_limit,>9999.99 high_limit> UNION ALL> SELECT 'Heavy Hitters' name, 10000 low_limit,>9999999.99 high_limit) groups> ON cust_rollup.cust_balance>BETWEEN groups.low_limit AND groups.high_limit> GROUP BY groups.name;+++| name| num_customers |+++| Average Joes |2 || Heavy Hitters |4 || Small Fry|5 |+++3 rows in set (0.01 sec)В блоке from имеется два подзапроса: первый подзапрос, cust_rollup,возвращает общий остаток по депозитным счетам для каждого клиента,а второй подзапрос, groups, формирует таблицу, содержащую три группы клиентов.

Вот таблица, сгенерированная подзапросом cust_rollup:mysql> SELECT SUM(a.avail_balance) cust_balance> FROM account a INNER JOIN product p> ON a.product_cd = p.product_cd> WHERE p.product_type_cd = 'ACCOUNT'> GROUP BY a.cust_id;++| cust_balance |++|4557.75 ||2458.02 ||3270.25 ||6788.98 ||2237.97 ||10122.37 ||5000.00 ||3875.18 ||10971.22 ||23575.12 ||38552.05 |++11 rows in set (0.05 sec)Затем таблица, сгенерированная подзапросом cust_rollup, соединяетсяс таблицей groups посредством условия вхождения в диапазон (cust_rolИспользование подзапросов187lup.cust_balance BETWEEN groups.low_limit AND groups.high_limit). Наконец, соединенные данные группируются и подсчитывается число клиентов в каждой группе для формирования окончательного результирующего набора.Конечно, можно было бы не использовать подзапрос, а просто создатьпостоянную таблицу для хранения описаний групп. При таком подходе через некоторое время БД изобиловала бы небольшими специальными таблицами, причины появления которых мало кто помнил бы.Мне приходилось работать в средах, где пользователям БД позволялось создавать собственные таблицы для специальных целей.

Результаты были просто губительными (таблицы, не включенные в резервные копии; таблицы, потерянные при обновлениях сервера; простоисервера изза проблем распределения памяти и т. д.). Однако, вооружившись запросами, можно придерживаться политики, при которойтаблицы добавляются в БД, только если есть очевидная необходимостьхранения новых данных.Подзапросы, ориентированные на задачиВ системах, используемых для создания отчетов или наборов данных,часто встречаются следующие запросы:mysql> SELECT p.name product, b.name branch,> CONCAT(e.fname, ' ', e.lname) name,> SUM(a.avail_balance) tot_deposits> FROM account a INNER JOIN employee e> ON a.open_emp_id = e.emp_id> INNER JOIN branch b> ON a.open_branch_id = b.branch_id> INNER JOIN product p> ON a.product_cd = p.product_cd> WHERE p.product_type_cd = 'ACCOUNT'> GROUP BY p.name, b.name, e.fname, e.lname;+++++| 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| Quincy Branch | John Blake|1057.75 || checking account| So.

NH Branch | Theresa Markham |67852.33 || checking account| Woburn Branch | Paula Roberts |3315.77 || 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| So. NH Branch | Theresa Markham |387.99 || savings account| Woburn Branch | Paula Roberts |700.00 |+++++11 rows in set (0.02 sec)188Глава 9. ПодзапросыЭтот запрос суммирует все остатки депозитных счетов по типу счета,сотруднику, открывшему счета, и отделениям, в которых были открыты счета.

Если внимательнее посмотреть на запрос, увидим, что таблицы product, branch и employee нужны только в целях отображения и чтовсе необходимое для группировки (product_cd, open_branch_id, open_emp_idи avail_balance) есть в таблице account. Поэтому задачу по формированию групп можно было бы выделить в подзапрос, а затем для получения нужного результата соединить остальные три таблицы с таблицей, сгенерированной подзапросом. Вот подзапрос группировки:mysql> SELECT product_cd, open_branch_id branch_id, open_emp_id emp_id,> SUM(avail_balance) tot_deposits> FROM account> GROUP BY product_cd, open_branch_id, open_emp_id;+++++| product_cd | branch_id | emp_id | tot_deposits |+++++| BUS|2 |10 |9345.55 || BUS|4 |16 |0.00 || CD|1 |1 |11500.00 || CD|2 |10 |8000.00 || CHK|1 |1 |782.16 || CHK|2 |10 |3315.77 || CHK|3 |13 |1057.75 || CHK|4 |16 |67852.33 || MM|1 |1 |14832.64 || MM|3 |13 |2212.50 || SAV|1 |1 |767.77 || SAV|2 |10 |700.00 || SAV|4 |16 |387.99 || SBL|3 |13 |50000.00 |+++++14 rows in set (0.01 sec)Это – сердце запроса; все остальные таблицы нужны только для того,чтобы обеспечить осмысленные строки для шапки таблицы вместо именстолбцов внешних ключей product_cd, open_branch_id и open_emp_id.

Следующий запрос включает запрос к таблице account в качестве подзапросаи соединяет результирующую таблицу с тремя остальными таблицами:mysql> SELECT p.name product, b.name branch,> CONCAT(e.fname, ' ', e.lname) name,> account_groups.tot_deposits> FROM> (SELECT product_cd, open_branch_id branch_id,>open_emp_id emp_id,>SUM(avail_balance) tot_deposits> FROM account> GROUP BY product_cd, open_branch_id, open_emp_id) account_groups> INNER JOIN employee e ON e.emp_id = account_groups.emp_id> INNER JOIN branch b ON b.branch_id = account_groups.branch_idИспользование подзапросов189> INNER JOIN product p ON p.product_cd = account_groups.product_cd> WHERE p.product_type_cd = 'ACCOUNT';+++++| product| branch| name| tot_deposits |+++++| certificate of deposit | Headquarters | Michael Smith |11500.00 || checking account| Headquarters | Michael Smith |782.16 || money market account | Headquarters | Michael Smith |14832.64 || savings account| Headquarters | Michael Smith |767.77 || certificate of deposit | Woburn Branch | Paula Roberts |8000.00 || checking account| Woburn Branch | Paula Roberts |3315.77 || savings account| Woburn Branch | Paula Roberts |700.00 || checking account| Quincy Branch | John Blake|1057.75 || money market account | Quincy Branch | John Blake|2212.50 || checking account| So.

Характеристики

Список файлов ответов (шпаргалок)