Мартин Грубер - Понимание SQL (Мартин Грубер. Понимание SQL), страница 14

Напишите запрос, который вычислил бы сумму комиссионных продавца длякаждого порядка заказчика с оценкой выше 100.(См. Приложение A для ответов.)9ОБЪЕДИНЕНИЕ ТАБЛИЦЫС СОБОЙВ ГЛАВЕ 8 МЫ ПОКАЗАЛИ ВАМ, КАК ОБЪЕДИНЯТЬ ДВЕ или более таблиц —вместе.Достаточно интересно то, что та же самая методика может использоваться чтобы объединять вместе две копии одиночной таблицы.В этой главе, мы будем исследовать этот процесс. Как вы видете, объединениетаблицы с самой собой, далеко не простая вещь, и может быть очень полезным способом определять определенные виды связей между пунктами данных в конкретнойтаблице.КАК ДЕЛАТЬ ОБЪЕДИНЕНИЕ ТАБЛИЦЫ С СОБОЙ ?Для объединения таблицы с собой, вы можете сделать каждую строку таблицы,одновременно, и комбинацией ее с собой и комбинацией с каждой другой строкойтаблицы.

Вы затем оцениваете каждую комбинацию в терминах предиката, также какв обьединениях мультитаблиц. Это позволит вам легко создавать определенные видысвязей между различными позициями внутри одиночной таблицы — с помощью обнаружения пар строк со значением поля, например.Вы можете изобразить обьединение таблицы с собой, как обьединение двух копий одной и той же таблицы. Таблица на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано.Другими словами, это обьединение — такое же, как и любое другое обьединениемежду двумя таблицами, за исключением того, что в данном случае обе таблицыидентичны.ПСЕВДОНИМЫСинтаксис команды для объединения таблицы с собой, тот же что и для объединения многочисленых таблиц, в одном экземпляре.Когда вы объединяете таблицу с собой, все повторяемые имена столбца, заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, вы должны иметь два различных имени для этой таблицы.Вы можете сделать это с помощью определения временных имен называемых— переменными диапазона, переменными корреляции или просто — псевдонимами.Вы определяете их в предложении FROM запроса.

Это очень просто: вы набираете имя таблицы, оставляете пробел, и затем набираете псевдоним для нее.Имеется пример, который находит все пары заказчиков имеющих один и тот жесамый рейтинг (вывод показывается в Рисунке 9.1):SELECT first.cname, second.cname, first.ratingFROM Customers first, Customers secondWHERE first.rating = second.rating;=============== SQL Execution Log ==============|GiovanniGiovanni200||GiovanniLiu200||LiuGiovanni200||LiuLiu200||GrassGrass300||GrassCisneros300||ClemensHoffman100||ClemensClemens100||ClemensPereira100||CisnerosGrass300||CisnerosCisneros300||PereiraHoffman100||PereiraClemens100||PereiraPereira100|=================================================Рисунок 9.1: Объединение таблицы с собой(Обратите внимание что на Рисунке 9.1, как и в некоторых дальнейших примерах, полный запрос не может уместиться в окне вывода, и следовательно будет усекаться.)В вышеупомянутой команде, SQL ведет себя так, как если бы он соединял дветаблицы называемые 'первая' и 'вторая'.

Обе они — фактически, таблицы Заказчика,но псевдонимы разрешают им быть обработаными независимо. Псевдонимы первый ивторой были установлены в предложении FROM запроса, сразу после имени копиитаблицы.Обратите внимание что псевдонимы могут использоваться в предложенииSELECT, даже если они не определены в предложении FROM.Это — очень хорошо. SQL будет сначала допускать любые такие псевдонимы наверу, но будет отклонять команду если они не определены далее в предложенииFROM запроса.Псевдоним существует только пока команда выполняется. Когда запрос заканчивается, псевдонимы, используемые в нем, больше не имеют никакого значения.Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с ними,SQL может обрабатывать эту операцию точно также, как и любое другое обьединение— берет каждую строку из одного псевдонима и сравнивает ее с каждой строкой издругого псевдонима.УСТРАНЕНИЕ ИЗБЫТОЧНОСТИОбратите внимание, что наш вывод имеет два значение для каждой комбинации,причем второй раз в обратном порядке.

Это потому, что каждое значение показанопервый раз в каждом псевдониме, и второй раз (симметрично) в предикате.Следовательно, значение A в псевдониме сначала выбирается в комбинации созначением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме.

В нашем примере,Hoffman выбрался вместе с Clemens, а затем Clemens выбрался вместе с Hoffman.Тот же самый случай с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того каждая строка была сравнена сама с собой, чтобы вывести строки такие как — Liu и Liu.Простой способ избежать этого состoит в том, чтобы налагать порядок на двазначения, так чтобы один мог быть меньше чем другой или предшествовал ему в алфавитном порядке.

Это делает предикат ассиметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например:SELECT tirst.cname, second.cname, first.ratingFROM Customers first, Customers secondWHERE first.rating = second.ratingAND 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|| =============================================== ||cnamecnamerating|| ------- --------------|| HoffmanPereira100|| GiovanniLiu200|| ClemensHoffman100|| PereiraPereira100|| GisnerosGrass300|=================================================Рисунок 9.2: Устранение избыточности вывода в обьединении с собой.Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе.

Когда та же самаякомбинация появляется в обратном порядке — когда Periera в псевдониме первойтаблицы сравнтвается с Hoffman во второй таблице псевдонима — второе условие невстречается. Аналогично, Hoffman не выбирается при наличии того же рейтинга что ион сам потому что его имя не предшествует ему самому в алфавитном порядке. Еслибы вы захотели включить сравнение строк с ними же в запросах подобно этому, вымогли бы просто использовать <= вместо <.ПРОВЕРКА ОШИБОКТаким образом мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Порядков, вы можете видеть чтополя cnum и snum должны иметь постоянную связь.Так как каждый заказчик должен быть назначен к одному и только одному продавцу, каждый раз когда определенный номер заказчика появляется в таблице Порядков, он должен совпадать с таким же номером продавца. Следующая команда будетопределять любые несогласованности в этой области:SELECT first.onum, first.cnum, first.snum,second.onum, second.cnum, second.snumFROM Orders first, Orders secondWHERE first.cnum = second.cnumAND 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.cnumFROM Customers a, Customers b, Customers cWHERE a.rating = 100 AND b.rating = 200 AND c.rating = 300;=============== SQL Execution Log ==============|cnumcnumcnum|| --------------||200120022004||200120022008||200120032004||200120032008||200620022004||200620022008||200620032004||200620032008||200720022004||200720022008||200720032004||200720032008|=================================================Рисунок 9.3: Комбинация пользователей с различными значениями рейтингаКак вы можете видеть, этот запрос находит все комбинации заказчиков с тремязначениями оценки, поэтому первый столбец состоит из заказчиков с оценкой 100,второй с 200, и последний с оценкой 300.

Они повторяются во всех возможных комбинациях. Это — сортировка группировки которая не может быть выполнена с GROUPBY или ORDER BY, поскольку они сравнивают значения только в одном столбце вывода.Вы должны также понимать, что не всегда обязательно использовать каждыйпсевдоним или таблицу которые упомянуты в предложении FROM запроса, в предложении SELECT. Иногда, предложение или таблица становятся запрашиваемыми исключительно потому что они могут вызываться в предикате запроса. Например,следующий запрос находит всех заказчиков размещенных в городах где продавецSerres (snum 1002) имеет заказиков (вывод показывается в Рисунке 9.4):SELECT b.cnum, b.cnameFROM Customers a, Customers bWHERE a.snum = 1002AND 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;|| ==============================================||cnumcname|| -------------||2003Liu||2008Cisneros||2004Grass|===============================================Рисунок 9.4: Нахождение заказчиков в городах относящихся к Serres.Псевдоним a будет делать предикат неверным за исключением случая когда егозначение столбца snum = 1002.