HTML  CSS  PHP и MySQL  Регулярные выражения  Введение в SQL  Учебник SQL

Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

SQL

Глава 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 ведёт себя так, как если бы он соединял две таблицы, называемые first и second. Обе они — фактически — таблица Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы first и second были установлены в предложении FROM запроса сразу после имени таблицы.

Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM. Это очень удобно. SQL будет сначала принимать любые такие псевдонимы на веру, но будет отклонять команду, если они не определены далее в предложении FROM запроса.

Псевдоним существует, только пока команда выполняется! Когда запрос заканчивается, псевдонимы, используемые в нём, больше не имеют никакого значения.

Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с ними, SQL может обрабатывать эту операцию точно так же, как и любое другое объединение: берёт каждую строку из одного псевдонима и сравнивает её с каждой строкой из другого псевдонима.

Устранение избыточности

Обратите внимание, что наш вывод имеет два значения для каждой комбинации, причем второй раз — в обратном порядке. Это потому что каждое значение показано первый раз в каждом псевдониме, а второй раз (симметрично) — в предикате. Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Hoffman выбран вместе с Clemens, а затем Clemens выбран вместе с Hoffman. Тот же самый случай — с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того, каждая строка была сравнена сама с собой, чтобы вывести строки, такие как Liu и Liu.

Простой способ избежать этого — установить порядок на два значения так, чтобы одно могло быть меньше, чем другое, или предшествовать ему в алфавитном порядке. Это делает предикат асимметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например:

            
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 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 Устранение избыточности вывода в 
               объединении с собой

Hoffman предшествует Pereira в алфавитном порядке, поэтому комбинация удовлетворяет обоим условиям предиката и появляется в выводе. Когда та же сама комбинация появляется в обратном порядке — когда Pereira в псевдониме первой таблицы сравнивается с Hoffman во второй таблице псевдонима — второе условие не выполняется. Аналогично Hoffman не выбирается при наличии того же рейтинга, что и у него самого, потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же самими в запросах, подобно этому, вы могли бы просто использовать <= вместо <.

Проверка ошибок

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

Следующая команда будет определять любые несогласованности в этой области:

             
SELECT first.onum, first.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 ==============
  |                                                 |
  |   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, поскольку они сравнивают значения только в одном столбце вывода.

Вы должны также понимать, что не всегда обязательно использовать в предложении SELECT каждый псевдоним или таблицу, которые упомянуты в предложении FROM запроса. Иногда предложение или таблица становятся запрашиваемыми исключительно потому, что они могут вызываться в предикате запроса. Например, следующий запрос находит всех заказчиков, размещённых в городах, где продавец 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) всех заказчиков.

(Ответы см. в Приложении А.)