Мартин Грубер - Понимание SQL (991940), страница 16
Текст из файла (страница 16)
ANSI также предохраняет вас от появления обеихзначений при сравнении, которые нужно вывести с помощью подзапроса.ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ ВПОДЗАПРОСАХОдин тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, — агрегатная функция. Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будетвыбирать одиночное значение для использования в основном предикате. Например,вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-еОктября (вывод показан на Рисуноке 10.3):SELECT *FROM OrdersWHERE amt > ( SELECT AVG (amt)FROM OrdersWHERE odate = 10/04/1990 );=============== SQL Execution Log ==============| SELECT *|| FROM Orders|| WHERE amt >|| (SELECT AVG (amt)|| FROM Orders|| WHERE odate = 01/04/1990 );|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- --------- ||30021900.10 10/03/199020071004 ||30052345.45 10/03/199020031002 ||30061098.19 10/03/199020081007 ||30091713.23 10/04/199020021003 ||30084723.00 10/05/199020061001 ||30101309.95 10/06/199020041002 ||30119891.88 10/06/199020061001 |=================================================Рисунок 10.3: Выбор всех сумм со значением выше средней на 10/04/1990Средняя сумма приобретений на 4 Октября — 1788.98 (1713.23 + 75.75) делитсяпополам, что в целом равняется = 894.49.
Все строки со значением в поле amt вышеэтого — являются выбраными.Имейте в виду, что сгруппированные агрегатные функции, которые являются агрегатными функциями определенными в терминах предложения GROUP BY, могутпроизводить многочисленые значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будетотклонена в принципе. Вы должны использовать одиночную агрегатную функцию спредложением WHERE что устранит нежелательные группы. Например, следующийзапрос который должен найти среднее значение комиссионных продавца в Лондоне —SELECT AVG (comm)FROM SalespeopleGROUP BY cityHAVlNG city = "London";не может использоваться в подзапросе! Во всяком случае это не лучший способ формировать запрос.
Другим способом может бытьSELECT AVG (comm)FROM SalespeopleWHERE city = "London";ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ, КОТОРЫЕ ВЫДАЮТМНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА INВы можете использовать подзапросы которые производят любое число строкесли вы используете специальный оператор IN (операторы BETWEEN, LIKE, и ISNULL не могут использоваться с подзапросами). Как вы помните, IN определяет наборзначений, одно из которых должно совпадать с другим термином уравнения предикатав порядке, чтобы предикат был верным. Когда вы используете IN с подзапросом, SQLпросто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос, который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца вЛондоне (вывод показывается в Рисунке 10.4):SELECT *FROM OrdersWHERE snum IN ( SELECT snumFROM SalespeopleWHERE city = "LONDON" );=============== SQL Execution Log ==============| SELECT *|| FROM Orders|| WHERE snum IN|| (SELECT snum|| FROM Salespeople|| WHERE city = 'London');|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- ---------- ||3003767.19 10/03/199020011001 ||30021900.10 10/03/199020071004 ||30061098.19 10/03/199020081007 ||30084723.00 10/05/199020061001 ||30119891.88 10/06/199020061001 |=================================================Рисунок 10.4: Использование подзапроса с INВ ситуации подобно этой, подзапрос более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали обьединение:SELECT onum, amt, odate, cnum, Orders.snumFROM Orders, SalespeopleWHERE Orders.snum = Salespeople.snumAND Salespeople.city = "London";Хотя это и произведет тот же самый вывод что и в примере с подзапросом, SQLдолжен будет просмотреть каждую возможную комбинацию строк из двух таблиц ипроверить их снова по составному предикату.Проще и эффективнее извлекать из таблицы Продавцов значения поля snum гдеcity = "London", и затем искать эти значения в таблице Порядков, как это делается вварианте с подзапросом.
Внутренний запрос дает нам snums=1001 и snum=1004.Внешний запрос, затем, дает нам строки из таблицы Порядков, где эти поля snumнайдены.Строго говоря, быстрее или нет работает вариант подзапроса, практически зависит от реализации — в какой программе вы это используете. Эта часть вашей программы, называемая оптимизатор, пытается найти наиболее эффективный способвыполнения ваших запросов.Хороший оптимизатор во всяком случае преобразует вариант обьединения вподзапрос, но нет достаточно простого способа для вас, чтобы выяснить, выполненоэто или нет. Лучше сохранить ваши запросы в памяти, чем полагаться полностью наоптимизатор.Конечно, вы можете также использовать оператор IN, даже когда вы уверены,что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN.
В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачуесли больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком.Вы не увидите непосредственно вывода из подзапросов; если вы полагаете, чтоподзапрос собирается произвести только одно значение, а он производит различные.Вы не сможете объяснить различия в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:SELECT onum, amt, odateFROM OrdersWHERE snum = ( SELECT snumFROM OrdersWHERE cnum = 2001 );Вы можете устранить потребность в DISTINCT, используя IN вместо (=), подобноэтому:SELECT onum, amt, odateFROM OrdersWHERE snum IN ( SELECT snumFROM OrdersWHERE cnum = 2001 );Что случится, если есть ошибка и один из порядков был акредитован к различным продавцам? Версия, использующая IN, будет давать вам все порядки для обоихпродавцов.
Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения, сделанные на основе этого запроса, не будут содержать ошибки. Вариант, использующий (=), просто потерпит неудачу.Это, по крайней мере, позволило бы вам узнать, что имеется такая проблема.Вы должны затем выполнять поиск неисправности, выполнив этот подзапрос отдельнои наблюдая значения, которые он производит.В принципе, если вы знаете, что подзапрос должен (по логике) вывести толькоодно значение, вы должны использовать =. IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того ожидаетевы их или нет.Предположим, мы хотим знать комиссионные всех продавцов обслуживающихзаказчиков в Лондоне:SELECT commFROM SalespeopleWHERE snum IN ( SELECT snumFROM CustomersWHERE city = "London" );Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значениякомиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне.
Это — только для данного случая. Нет никакой причины чтобы некоторые заказчики в Лондоне не могли быть назначеными к кому-то еще. Следовательно, IN — этонаиболее логичная форма чтобы использовать ее в запросе.=============== SQL Execution Log ==============| SELECT comm|| FROM Salespeople|| WHERE snum IN|| (SELECT snum|| FROM Customers|| WHERE city = 'London');|| =============================================== ||comm|| ------||0.12|=================================================Рисунок 10.5: Использование IN с подзапросом для вывода одногозначенияМежду прочим, префикс таблицы для поля city необязателен в предыдущемпримере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов.SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса.
Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает чтоимеется ссылка к Customer.city (поле city таблицы Заказчиков).Так как таблица Заказчиков указана в предложении FROM текущего запроса,SQL предполагает что это — правильно. Это предположение может быть отмененополным именем таблицы или префиксом псевдонима, о которых мы поговорим позжекогда будем говорить об соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫСмысл всех подзапросов обсужденных в этой главе тот, что все они выбираютодиночный столбец.
Это обязательно, поскольку выбранный вывод сравнивается содиночным значением. Подтверждением этому то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются соператором EXISTS, который мы будем представлять в Главе 12.ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХВы можете использовать выражение основанное на столбце, а не просто самстолбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос используетреляционный оператор = (вывод показывается в Рисунке 10.6):SELECT *FROM CustomersWHERE cnum = ( SELECT snum + 1000FROM SalespeopleWHERE sname = “Serres” );=============== SQL Execution Log ============| SELECT *|| FROM Customers|| WHERE cnum =|| (SELECT snum + 1000|| WHERE Salespeople|| WHERE sname = 'Serres'|| ============================================= ||cnumcnamecityratingsnum|| -----------------------||2002GiovanniRome2001003|===============================================Рисунок 10.6: Использование подзапроса с выражениемОн находит всех заказчиков, чье значение поля cnum равное 1000, выше поляsnum Serres.
Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, илиограничением UNIQUE, обсуждаемым в Главе 18); иначе подзапрос может произвестимногочисленые значения. Когда поля snum и сnum не имеют такого простого функционального значения как например первичный ключ, что не всегда хорошо, запростипа вышеупомянутого невероятно полезен.ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVINGВы можете также использовать подзапросы внутри предложения HAVING. Этиподзапросы могут использовать свои собственные агрегатные функции если они непроизводят многочисленых значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером (вывод показывается в Рисунке 10.7):SELECT rating, COUNT ( DISTINCT cnum )FROM CustomersGROUP BY ratingHAVING rating > ( SELECT AVG (rating)FROM CustomersWHERE 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|| --------------||2002|===============================================Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San JoseЭта команда подсчитывает заказчиков с оценками выше среднего в San Jose.Так как имеются другие оценки отличные от 300, они должны быть выведены с числомномеров заказчиков которые имели эту оценку.РЕЗЮМЕТеперь вы используете запросы в иерархической манере.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
