SQLГлава 9. Объединение таблицы с собойВ Главе 8 мы рассмотрели, как объединить две или более таблиц. Интересно, что та же самая методика может использоваться для объединения двух копий одной таблицы. В этой главе мы будем исследовать этот процесс. Как вы увидите, объединение таблицы с самой собой — далеко не простая вещь и может быть очень полезна для определения некоторых видов связей между данными в конкретной таблице. Как выполнить объединение таблицы с собой?Для объединения таблицы с собой вы можете сделать каждую строку таблицы одновременно и комбинацией её с собой, и комбинацией с каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терминах предиката так же, как в объединениях нескольких таблиц. Это позволит легко создавать определенные виды связей между различными позициями внутри одной таблицы с помощью обнаружения, например, пар строк со значением поля. Вы можете представить объединение таблицы с собой как объединение двух копий одной и той же таблицы. Таблица на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано. Другими словами: это такое же объединение, как и любое другое объединение между двум таблицами, за исключением того, что в данном случае обе таблицы идентичны. ПсевдонимыСинтаксис команды для объединения таблицы с собой — тот же, что и для объединения нескольких таблиц. Когда вы объединяете таблицу с собой, все одинаковые имена столбца дополняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, вы должны иметь два различных имени для этой таблицы. Вы можете сделать это с помощью определения временных имён, называемых "переменными диапазона", "переменными корреляции" или просто "псевдонимами".
Вы определяете их в предложении Вот пример, который находит все пары заказчиков, имеющих один и тот же рейтинг (вывод показан на Рисунке 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 ведёт себя так, как если бы он соединял две таблицы, называемые
Обратите внимание, что псевдонимы могут использоваться в предложении Псевдоним существует, только пока команда выполняется! Когда запрос заканчивается, псевдонимы, используемые в нём, больше не имеют никакого значения. Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с ними, SQL может обрабатывать эту операцию точно так же, как и любое другое объединение: берёт каждую строку из одного псевдонима и сравнивает её с каждой строкой из другого псевдонима. Устранение избыточности
Обратите внимание, что наш вывод имеет два значения для каждой комбинации, причем второй раз — в обратном порядке. Это потому что каждое значение показано первый раз в каждом псевдониме, а второй раз (симметрично) — в предикате. Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Простой способ избежать этого — установить порядок на два значения так, чтобы одно могло быть меньше, чем другое, или предшествовать ему в алфавитном порядке. Это делает предикат асимметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например: 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 Устранение избыточности вывода в объединении с собой
Проверка ошибок
Таким образом, мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Заказов вы видите, что поля Следующая команда будет определять любые несогласованности в этой области: 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;
Хотя это выглядит сложно, логика достаточно проста. Команда будет брать первую строку таблицы Заказов, запоминать её под первым псевдонимом и проверять её в комбинации с каждой строкой таблицы Заказов под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдёт строку, где поле Больше псевдонимов
Хотя объединение таблицы с собой это первая ситуация, когда ясна необходимость наличия псевдонимов, вы не ограничены в их использовании тем, чтобы только отличать копию одной таблицы от её оригинала. Вы можете использовать псевдонимы в любое время, когда вы хотите создать альтернативные имена для ваших таблиц в команде. Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа Комплексные объединения
Вы можете использовать любое количество псевдонимов для одной таблицы в запросе, хотя использование более двух в данном предложении 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 Комбинация пользователей с различными значениями рейтинга
Как видите, этот запрос находит все комбинации заказчиков с тремя значениями оценки, поэтому первый столбец состоит из заказчиков с оценкой
Вы должны также понимать, что не всегда обязательно использовать в предложении 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 будет делать предикат неверным, за исключением случая, когда его значение столбца Вы можете также создать объединение, которое включает и различные таблицы, и псевдонимы одиночной таблицы. Следующий запрос объединяет таблицу Заказчики с собой, чтобы найти все пары заказчиков, обслуживаемых одним продавцом. В то же самое время этот запрос объединяет заказчика с таблицей Продавцов с именем этого продавца (вывод показан на Рисунке 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
(Ответы см. в Приложении А.) |
||