Назад в раздел
InfoCity
В ГЛАВЕ 8, МЫ ПОКАЗАЛИ ВАМ КАК ОБЪЕДИНЯТЬ ДВЕ или более таблиц -
вместе.
Достаточно интересно то, что та же самая методика может использо-
ваться чтобы объединять вместе две копии одиночной таблицы. В этой
главе, мы будем исследовать этот процесс. Как вы видете, объединение
таблицы с самой собой, далеко не простая вещь, и может быть очень по-
лезным способом определять определенные виды связей между пунктами
данных в конкретной таблице.
Для объединения таблицы с собой, вы можете сделать каждую строку
таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой
другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терми-
нах предиката, также как в обьединениях мультитаблиц. Это позволит вам
легко создавать определенные виды связей между различными позициями
внутри одиночной таблицы - с помощью обнаружения пар строк со значени-
ем поля, например.
Вы можете изобразить обьединение таблицы с собой, как обьединение
двух копий одной и той же таблицы. Таблица на самом деле не копирует-
ся, но SQL выполняет команду так, как если бы это было сделано. Други-
ми словами, это обьединение - такое же, как и любое другое обьединение
между двумя таблицами, за исключением того, что в данном случае обе
таблицы идентичны.
Синтаксис команды для объединения таблицы с собой, тот же что и для
объединения многочисленых таблиц, в одном экземпляре. Когда вы объеди-
няете таблицу с собой, все повторяемые имена столбца, заполняются пре-
фиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса,
вы должны иметь два различных имени для этой таблицы.
Вы можете сделать это с помощью определения временных имен называе-
мых переменными диапазона, переменными корреляции или просто - псевдо-
нимами. Вы определяете их в предложении FROM запроса. Это очень прос-
то: вы набираете имя таблицы, оставляете пробел, и затем набираете
псевдоним для нее. Имеется пример который находит все пары заказчиков
имеющих один и тот же самый рейтинг (вывод показывается в Рисунке
9.1):
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;
=============== SQL Execution Log ==============
| |
| Giovanni Giovanni 200 |
| Giovanni Liu 200 |
| Liu Giovanni 200 |
| Liu Liu 200 |
| Grass Grass 300 |
| Grass Cisneros 300 |
| Clemens Hoffman 100 |
| Clemens Clemens 100 |
| Clemens Pereira 100 |
| Cisneros Grass 300 |
| Cisneros Cisneros 300 |
| Pereira Hoffman 100 |
| Pereira Clemens 100 |
| Pereira Pereira 100 |
| |
===============================================
Рисунок 9.1: Объединение таблицы с собой
( обратите внимание что на Рисунке 9.1, как и в некоторых дальнейших
примерах, полный запрос не может уместиться в окне вывода, и следова-
тельно будет усекаться. )
В вышеупомянутой команде, SQL ведет себя так, как если бы он соеди-
нял две таблицы называемые 'первая' и 'вторая'. Обе они - фактически,
таблицы Заказчика, но псевдонимы разрешают им быть обработаными неза-
висимо. Псевдонимы первый и второй были установлены в предложении FROM
запроса, сразу после имени копии таблицы. Обратите внимание что псев-
донимы могут использоваться в предложении SELECT, даже если они не оп-
ределены в предложении FROM.
Это - очень хорошо. SQL будет сначала допускать любые такие псевдо-
нимы на веру, но будет отклонять команду если они не определены далее
в предложении FROM запроса.
Псевдоним существует - только пока команда выполняется ! Когда зап-
рос заканчивается, псевдонимы используемые в нем больше не имеют ника-
кого значения.
Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с
ними, SQL может обрабатывать эту операцию точно также как и любое дру-
гое обьединение - берет каждую строку из одного псевдонима и сравнива-
ет ее с каждой строкой из другого псевдонима.
Обратите внимание что наш вывод имеет два значение для каждой комби-
нации, причем второй раз в обратном порядке. Это потому, что каждое
значение показано первый раз в каждом псевдониме, и второй раз( сим-
метрично) в предикате. Следовательно, значение A в псевдониме сначала
выбирается в комбинации со значением B во втором псевдониме, а затем
значение A во втором псевдониме выбирается в комбинации со значением B
в первом псевдониме. В нашем примере, Hoffman выбрался вместе с Cle-
mens, а затем Clemens выбрался вместе с Hoffman. Тот же самый случай с
Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того каждая стро-
ка была сравнена сама с собой, чтобы вывести строки такие как - Liu и
Liu. Простой способ избежать этого состoит в том, чтобы налагать поря-
док на два значения, так чтобы один мог быть меньше чем другой или
предшествовал ему в алфавитном порядке. Это делает предикат ассимет-
ричным, поэтому те же самые значения в обратном порядке не будут выб-
раны снова, например:
SELECT tirst.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cname < second.cname;
Вывод этого запроса показывается в Рисунке 9.2.
Hoffman предшествует Periera в алфавитном порядке, поэтому комбина-
ция удовлетворяет обеим условиям предиката и появляется в выводе. Ког-
да та же самая комбинация появляется в обратном порядке - когда Perie-
ra в псевдониме первой таблицы сравнтвается с Hoffman во второй табли-
це псевдонима - второе условие не встречается. Аналогично Hoffman не
выбирается при наличии того же рейтинга что и он сам потому что его
имя не предшествует ему самому в алфавитном порядке. Если бы вы захо-
=============== SQL Execution Log ==============
| |
| SELECT first.cname, second.cname, first.rating |
| FROM Customers first, Customers second |
| WHERE first.rating = second.rating |
| AND first.cname < second.cname |
| =============================================== |
| cname cname rating |
| ------- --------- ------- |
| Hoffman Pereira 100 |
| Giovanni Liu 200 |
| Clemens Hoffman 100 |
| Pereira Pereira 100 |
| Gisneros Grass 300 |
=================================================
Рисунок 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;
=============== SQL Execution Log ==============
| |
| AND c.rating = 300; |
| =============================================== |
| cnum cnum cnum |
| ----- ------ ------ |
| 2001 2002 2004 |
| 2001 2002 2008 |
| 2001 2003 2004 |
| 2001 2003 2008 |
| 2006 2002 2004 |
| 2006 2002 2008 |
| 2006 2003 2004 |
| 2006 2003 2008 |
| 2007 2002 2004 |
| 2007 2002 2008 |
| 2007 2003 2004 |
| 2007 2003 2008 |
=================================================
Рисунок 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;
=============== SQL Execution Log ============
| |
| SELECT b.cnum, b.cname |
| FROM Customers a, Customers b |
| WHERE a.snum = 1002 |
| AND b.city = a.city; |
| ==============================================|
| cnum cname |
| ------ --------- |
| 2003 Liu |
| 2008 Cisneros |
| 2004 Grass |
=============================================
Рисунок 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;
=============== SQL Execution Log ==================
| |
| SELECT cname, 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; |
| ====================================================|
| cname snum cname cname |
| ------ ------ -------- -------- |
| Serres 1002 Liu Grass |
| Peel 1001 Hoffman Clemens |
=====================================================
Рисунок 9.5: Объединение таблицы с собой и с другой таблицей
Теперь Вы понимаете возможности объединения и можете использовать их
для ограничения связей с таблицей, между различными таблицами, или в
обоих случаях. Вы могли видеть некоторые возможности объединения при
использовании его способностей. Вы теперь познакомились с терминами
порядковые переменные, корреляционные переменные и предложения (эта
терминология будет меняться от изделия к изделию, так что мы предлага-
ем Вам познакомится со всеми тремя терминами ). Кроме того Вы поняли,
немного, как в действительности работают запросы.
Следующим шагом после комбинации многочисленых таблиц или многочис-
леных копий одной таблицы в запросе, будет комбинация многочисленных
запросов, где один запрос будет производить вывод который будет затем
управлять работой другого запроса. Это другое мощное средство SQL, о
котором мы расскажем в Главе 10 и более тщательно в последующих гла-
вах.
1. Напишите запрос который бы вывел все пары продавцов живущих в одном
и том же городе. Исключите комбинации продавцов с ними же, а также
дубликаты строк выводимых в обратным порядке.
2. Напишите запрос который вывел бы все пары порядков по данным заказ-
чикам, именам этих заказчиков, и исключал дубликаты из вывода, как
в предыдущем вопросе.
3. Напишите запрос который вывел бы имена(cname) и города(city) всех
заказчиков с такой же оценкой(rating) как у Hoffmanа. Напишите зап-
рос использующий поле cnum Hoffmanа а не его оценку, так чтобы оно
могло быть использовано если его оценка вдруг изменится.
|
|
 |
|
