Перейти к оглавлению
                     9. ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ
 
 
 
 
   В ГЛАВЕ  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 и более тщательно в  последующих  гла-
 вах.
 
 
               ************** РАБОТА С SQL **************
 
 
 1. Напишите запрос который бы вывел все пары продавцов живущих в одном
    и том же городе.  Исключите комбинации продавцов с ними же, а также
    дубликаты строк выводимых в обратным порядке.
 2. Напишите запрос который вывел бы все пары порядков по данным заказ-
    чикам,  именам этих заказчиков, и исключал дубликаты из вывода, как
    в предыдущем вопросе.
 3. Напишите  запрос  который вывел бы имена(cname) и города(city) всех
    заказчиков с такой же оценкой(rating) как у Hoffmanа. Напишите зап-
    рос использующий поле cnum Hoffmanа а не его оценку,  так чтобы оно
    могло быть использовано если его оценка вдруг изменится.
 
 
 ( См. Приложение A для ответов. )
 
 
 
Перейти к оглавлению
Hosted by uCoz