Понимание SQL

Таблица 10.2: Использование DISTINCT чтобы вынудить получение одного значения из подзапроса

Подзапрос установил что значение поля snum совпало с Hoffman - 1001, и затем основной запрос выделил все порядки с этим значением snum из таблицы Порядков( не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы знаем что каждая строка в таблице Порядков с данным значением cnum должна иметь такое же значение snum. Однако так как там может быть любое число таких строк, подзапрос мог бы вывести много (хотя и идентичных ) значений snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом.

Альтернативный подход должен быть чтобы ссылаться к таблице Заказчиков а не к таблице Порядков в подзапросе. Так как поле cnum - это первичный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Порядков но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое мы показали выше. (SQL имеет механизмы которые определяют - кто имеет привилегии чтобы делать что-то в определенной таблице. Это будет объясняться в Главе 22.) Пожалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она являеться исключением из правил.

ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ

Вы должны обратить внимание что предикаты включающие подзапросы, используют выражение

< скалярная форма > < оператор > < подзапрос >, а не

< подзапрос > < оператор > < скалярное выражение > или,

< подзапрос > < оператор > < подзапрос >.

Другими словами, вы не должны записывать предыдущий пример так:

SELECT *

FROM Orders

WHERE (SELECT DISTINCT snum

FROM Orders

WHERE cnum=2001 )

=snum;

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

ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ

Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, - агрегатная функция.

Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября (вывод показан в Таблице 10.3 ):

SELECT *

FROM Orders

WHERE amt >

( SELECT AVG (amt)

FROM Orders

WHERE odate=10/04/1990 );

SQL Execution Log

SELECT * FROM Orders WHERE amt >

(SELECT AVG (amt) FROM Orders

WHERE odate=01/04/1990 );

onum	amt	odate	cnum	snum
3002	1900.10	10/03/1990	2007	1004
3005	2345.45	10/03/1990	2003	1002
3006	1098.19	10/03/1990	2008	1007
3009	1713.23	10/04/1990	2002	1003
3008	4723.00	10/05/1990	2006	1001
3010	1309.95	10/06/1990	2004	1002
3011	9891.88	10/06/1990	2006	1001

Таблица 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 Salespeople

GROUP BY city

HAVlNG city="London";

не может использоваться в подзапросе! Во всяком случае это не лучший способ формировать запрос. Другим способом может быть -

SELECT AVG (comm)

FROM Salespeople

WHERE city="London";

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN