Мартин Грубер - Понимание SQL

SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second

WHERE first.rating=second.rating

AND first.cname < second.cname

Таблица 9.2: Устранение избыточности вывода в обьединении с собой.

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

могли бы просто использовать <=вместо <.

Таким образом мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Порядков, вы можете видеть что поля cnum и snum должны иметь постоянную связь. Так как каждый заказчик должен быть назначен к одному и только одному продавцу, каждый раз когда определенный номер заказчика появляется в таблице Порядков, он должен совпадать с таким же номером продавца. Следующая команда будет определять любые несогласованности в этой области:

SELECT first.onum, tirst.cnum, first.snum,

second.onum, second.cnum,second.snum

FROM Orders first, Orders second

WHERE first.cnum=second.cnum

AND first.snum < > second.snum;

Хотя это выглядит сложно, логика этой команды достаточно проста. Она будет брать первую строку таблицы Порядков, запоминать ее под первым псевдонимом, и проверять ее в комбинации с каждой строкой таблицы Порядков под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдет строку где поле cnum=2008 а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же самым значением поля cnum. Если он находит что какая -то из их имеет значение отличное от значения поля snum, предикат будет верен, и выведет выбранные поля из текущей комбинации строк. Если же значение snum с данным значением cnum в наш таблице совпадает, эта команда не произведет никакого вывода.

Хотя обьединение таблицы с собой - это первая ситуация когда понятно что псевдонимы необходимы, вы не ограничены в их использовании что бы только отличать копию одлной таблицы от ее оригинала. Вы можете использовать псевдонимы в любое время когда вы хотите создать альтернативные имена для ваших таблиц в команде. Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа a и b, и использовать их вместо имен таблицы в предложении SELECT и предикате. Они будут также использоваться с соотнесенными подзапросами(обсуждаемыми в Главе 11).

Вы можете использовать любое число псевдонимов для одной таблицы в запросе, хотя использование более двух в данном предложении SELECT * будет излишеством. Предположим что вы еще не назначили ваших заказчиков к вашему продавцу. Компании должна назначить каждому продавцу первоначально трех заказчиков, по одному для каждого рейтингового значения. Вы лично можете решить какого заказчика какому продавцу назначить, но следующий запрос вы используете чтобы увидеть все возможные комбинации заказчиков которых вы можете назначать. (Вывод показывается в Таблице 9.3 ):

SELECT a.cnum, b.cnum, c.cnum

FROM Customers a, Customers b, Customers c

WHERE a.rating=100

AND b.rating=200

AND c.rating=300;

AND c.rating=300;

Таблица 9.3 Комбинация пользователей с различными значениями рейтинга

Как вы можете видеть, этот запрос находит все комбинации заказчиков с тремя значениями оценки, поэтому первый столбец состоит из заказчиков с оценкой 100, второй с 200, и последний с оценкой 300. Они повторяются во всех возможных комбинациях. Это - сортировка группировки которая не может быть выполнена с GROUP BY или ORDER BY, поскольку они сравнивают значения только в одном столбце вывода.

Вы должны также понимать, что не всегда обязательно использовать каждый псевдоним или таблицу которые упомянуты в предложении FROM запроса, в предложении SELECT. Иногда, предложение или таблица становятся запрашиваемыми исключительно потому что они могут вызываться в предикате запроса. Например, следующий запрос находит всех заказчиков размещенных в городах где продавец Serres (snum 1002 ) имеет заказиков (вывод показывается в Таблице 9.4 ):

SELECT b.cnum, b.cname

FROM Customers a, Customers b

WHERE a.snum=1002

AND b.city=a.city;

SELECT b.cnum, b.cname

FROM Customers a, Customers b

WHERE a.snum=1002 AND b.city=a.city;

Таблица 9.4 Нахождение заказчиков в городах относящихся к Serres.

Псевдоним a будет делать предикат неверным за исключением случая когда его значение столбца snum= 1002. Таким образом псевдоним опускает все, кроме заказчиков продавца Serres. Псевдоним b будет верным для всех строк с тем же самым значением города что и текущее значение города для a; в ходе запроса, строка псевдонима b будет верна один раз когда значение города представлено в a. Нахождение этих строк псевдонима b - единственая цель псевдонима a, поэтоиму мы не выбираем все столбцы подряд. Как вы можете видеть, собственные заказчики Serres вы бираются при нахождении их в том же самом городе что и он сам, поэтому выбор их из псевдонима a необязателен. Короче говоря, псевдоним назходит строки заказчиков Serres, Liu и Grass. Псевдоним b находит всех заказчиков размещенных в любом из их городов (San Jose и Berlin соответственно ) включая, конечно, самих - Liu и Grass.

Вы можете также создать обьединение которое включает и различные таблицы и псевдонимы одиночной таблицы. Следующий запрос объединяет таблицу Пользователей с собой: чтобы найти все пары заказчиков обслуживаемых одним продавцом. В то же самое время, этот запрос объединяетзаказчика с таблицей Продавцов с именем этого продавца (вывод показан в Таблице 9.5 ):

SELECT sname, Salespeople.snum, first.cname

second.cname

FROM Customers first, Customers second, Salespeople

WHERE first.snum=second.snum

AND Salespeople.snum=first.snum

AND first.cnum < second.cnum;

SELECT cname, Salespeople.snum, first.cname second.cname

FROM Customers first, Customers second, Salespeople

WHERE first.snum=second.snum AND Salespeople.snum=first.snum