Программа по формированию навыков безопасного поведения на дорогах и улицах «Добрая дорога детства» 2
Скачать 2.46 Mb.
|
| Главе 12. ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ Вы можете использовать выражение, основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов, или с IN. Например, следующий запрос использует реляционный оператор = (вывод показан на Рисунке 10.6): SELECT * FROM Customers WHERE cnum = (SELECT snum + 1000 FROM Salespeople WHERE sname = Serres); Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18); иначе =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE cnum = | | (SELECT snum + 1000 | | WHERE Salespeople | | WHERE sname = 'Serres' | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2002 Giovanni Rome 200 1003 | ============================================= Рисунок 10.6 Использование подзапроса с выражением подзапрос может произвести несколько значений. Когда поля snum и сnum не имеют такого простого функционального значения как, например, первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен. ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят нескольких значений, или использовать GROUP BY или HAVING. Следующий запрос является примером этого (вывод показан на Рисунке 10.7): SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = " San Jose'); =============== SQL Execution Log ============= | | | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = 'San Jose'); | |================================================ | | rating | | -------- -------- | | 200 2 | ================================================ Рисунок 10.7 Поиск в San Jose заказчиков с оценкой выше среднего Эта команда подсчитывает заказчиков в San Jose с рейтингами выше среднего. Так как имеются другие оценки, отличные от 300, они должны быть выведены с числом номеров заказчиков, которые имели эту оценку. Коррелированные вложенные подзапросы Выдать название и статус поставщиков продукта с номером 11. SELECT Название, Статус FROM Поставщики WHERE 11 IN ( SELECT ПР FROM Поставки WHERE ПС = Поставщики.ПС ); Такой подзапрос отличается от рассмотренного в п.3.3.2 тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом: Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос ( SELECT ПР FROM Поставки WHERE ПС = 1 ); получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо. Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики. Подобные подзапросы называются коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка коррелированного подзапроса, следовательно, должна повторяться для каждого значения извлекаемого из внешнего подзапроса, а не выполняться раз и навсегда. Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе. Выдать номера всех продуктов, поставляемых только одним по-ставщиком.
Действие этого запроса можно пояснить следующим образом: "Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта (ПР), если и только если это значение не входит в некоторую строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС) в строке Y не равно его значению в строке X". Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним - либо X, либо Y. КАК РАБОТАЕТ EXISTS? EXISTS - это оператор, который производит верное или неверное значение, другими словами, выражение Бул ( см. Главу 4 для обзора этого термина ). Это означает что он может работать автономно в предикате или в комбинации с другими выражениями Бул использующими Булевые операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose ( вывод для этого запроса показывается в Рисунке 12.1 ): SELECT cnum, cname, city FROM Customers WHERE EXISTS ( SELECT * FROM Customers WHERE city = " San Jose' ); Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос( не соотнесенный ) был выполнен только один раз для всего внешнего запроса, и следовательно =============== SQL Execution Log ============ | | | SELECT snum, sname, city | | FROM Customers | | WHERE EXISTS | | (SELECT * | | FROM Customers | | WHERE city = 'San Jose'); | | ============================================= | | cnum cname city | | ----- -------- ---- | | 2001 Hoffman London | | 2002 Giovanni Rome | | 2003 Liu San Jose | | 2004 Grass Berlin | | 2006 Clemens London | | 2008 Cisneros San Jose | | 2007 Pereira Rome | ============================================= Рисунок 12.1 Использование оператора EXISTS <.pre> имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат верным или неверным для всех строк сразу, что это не так уж полезно для извлечения определенной информации. ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS В вышеупомянутом примере, EXISTS должен быть установлен так чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборе звезду( SELECT *) В этом состоит его отличие от подзапроса который Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает - выполняется или нет вывод из подзапроса - а не использует выведенные значения. ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЕННЫМИ ПОДЗАПРОСАМИ В соотнесенном подзапросе, предложение EXISTS оценивается отдельно для каждой строки таблицы им которой указано во внешнем запросе, точно также как и другие операторы предиката, когда вы используете соотнесенный подзапрос. Это дает возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы указанной в основном запросе. Следовательно информация из внутреннего запроса, будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов которые имеют многочисленных заказчиков (вывод для этого запроса показывается в Рисунке 12.2 ): SELECT DISTINCT snum FROM Customers outer WHERE EXISTS ( SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum ); =============== SQL Execution Log ============ | | | SELECT DISTINCT cnum | | FROM Customers outer | | WHERE EXISTS | | (SELECT * | | FROM Customers inner | | WHERE inner.snum = outer.snum | | AND inner.cnum < > outer.cnum); | | ============================================= | | cnum | | ----- | | 1001 | | 1002 | ============================================= Рисунок 12. 2: Использование EXISTS с соотнесенным подзапросом Для каждой строки-кандидата внешнего запроса ( представляющей заказчика проверяемого в настоящее время ), внутренний запрос находит строки которые совпадают со значением пол snum ( которое имел продавец ), но не со значением пол cnum ( соответствующего другим заказчикам ). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика обслуживаемых текущим продавцом (то есть продавцом заказчика в текущей строке-кандидата из внешнего запроса ). Предикат EXISTS поэтому верен для текущей строки, и номер продавца пол (snum) таблицы указанной во внешнем запросе будет выведено. Если был DISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика к которому он назначен. КОМБИНАЦИЯ ИЗ EXISTS И ОБЪЕДИНЕНИЯ Однако для нас может быть полезнее вывести больше информации об этих продавцах а не только их номера. Мы можем сделать это объединив таблицу Заказчиков с таблицей Продавцов ( вывод для запроса показывается в Рисунке 12.3 ): SELECT DISTINCT first.snum, sname, first.city FROM Salespeople first, Customers second WHERE EXISTS ( SELECT * FROM Customers third WHERE second.snum = third.snum AND second.cnum < > third.cnum ) AND first.snum = second.snum; =============== SQL Execution Log ============ | | | SELECT DISTINCT first.snum, sname, first.city | | FROM Salespeople first, Customers second | | WHERE EXISTS | | (SELECT * | | FROM Customers third | | WHERE second.snum = third.snum | | AND second.cnum < > third.cnum) | | AND first.snum = second.snum; | | ============================================= | | cnum cname city | | ----- -------- ---- | | 1001 Peel London | | 1002 Serres San Jose | ============================================= Рисунок 12.3: Комбинация EXISTS с объединением Внутренний запрос здесь - как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос - это объединение таблицы Продавцов с таблицей Заказчиков, наподобие того что мы видели прежде. Новое предложение основного предиката ( AND first.snum = second.snum ) естественно оценивается на том же самом уровне что и предложение EXISTS. Это - функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах пол snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в порядке для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях когда вторая часть запроса верна, а объединение - выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных. ИСПОЛЬЗОВАНИЕ NOT EXISTS Предыдущий пример дал понять что EXISTS может работать в комбинации с операторами Бул. Конечно, то что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS - это оператор NOT. Один из способов которым мы могли бы найти всех продавцов только с одним заказчиком будет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показывается в Рисунке 12.4:) SELECT DISTINCT snum FROM Customers outer WHERE NOT EXISTS ( SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum ); EXISTS И АГРЕГАТЫ Одна вещь которую EXISTS не может сделать - взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это - значение функции ; если же агрегатная функция не находит никаких строк, EXISTS неправилен. =============== SQL Execution Log ============ | | | SELECT DISTINCT snum | | FROM Salespeople outer | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers inner | | WHERE inner.snum = outer.snum | | AND inner.cnum < > outer.cnum); | | ============================================= | | cnum | | ----- | | 1003 | | 1004 | | 1007 | ============================================= Рисунок 12.4: Использование EXISTS с NOT Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет что проблема неверно решалась от начала до конца. Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели ( или который мы будем видеть ). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой они не могут быть использованы. Следующий раздел приводит этому пример. В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат - EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) - будет эквивалентен - EXISTS (SELECT sname FROM Salespeople) который был позволен выше. |
