интересно посмотреть, как оудет Бь[г.1ллеть p(?.iyjH>T npyiouiee множество, no;iy-ченное запросом без комбнпацпп MAX()/GROUP BY:

SELECT Free=CCASE bana WHEN Q ThEN title ELSE NULL END),

BadCc[npany=(CASE Land aI-EN 1 HEN ttle ELSE NULL END),

TheF1rm=(CASE banc WHEN 2 THEN ttie ELSE NULL END),

Scio=(CASE banc WHEN 3 THEN IVe ELSE NULL END) FROM #array

кого массива (на са.мом деле это простая перекрестная таблица) за один раз. Это осуществляется функцией CASE для каждого столбца. Тогда для каждой записи из начального результпруюитето множества все столбцы, кро.ме одного, будут и.меть значение NULL. Вот здесь нам на по.мощь и приходит комбинация МАХ()/ GROUP BY. Группируя по одно.му столбцу, .мы объединяем значения в каждо.м столбце, так что посторонние значения NULL удаляются. Использование МАХ() позволяет выбрать каждыг! столбец в процессе группировки (если в запросе присутствует предложение GROUP BY, все столбцы в списке SELECT, по которым не проводится группировка, должны быть либо агрегата.ми, либо константами). Заметьте, что агрегат MIN() также работа,! бы хорошо. Все, что нам на са.мом деле нужно, - агрегат, которьн! мог бы вернуть столбец title, - служит всего лишь для воз.можности использования GROUP BY, что противоречит обычно представлеитпо оттюшенрий между GROUP BY и агрегатами. Так как только агрегаты MIN() и МАХ() .могут возвращать си.мвольные значения, .мы должны применить один из них..

Сравнение массивов

Иногда бывает необходимо сравнить два массива или два подмножества одного массива между собой. Это может бьггь сложной задачей, поскольку для сравнения массивов необходи.мо использовать сортировку, что ие требуется для обыч-

ных плоских множеств. Вот модификация кода предыдущего примера, которая проверяет элементы табличного массива на равенство друг другу:

CREATE TABLE #аггау (band int. single int. title varcharOO))

INSERT #array VALUES(0.0.LITTLE BIT 0 LOVE):

INSERT #array VALUES(0.1.FIRE AND WATER):

INSERT #array VALUES(0.2.ALL RIGHT NOW):

INSERT #array VALUES(0.3.THE HUNTER):

INSERT #array VALUES(1.0.BAD COMPANY):

INSERT #array VALUES(1.1.SHOOTING STAR):

INSERT #array VALUES(1.2.FEEL LIKE MAKIN LOVE):

INSERT #array VALUES(1.3.ROCK AND ROLL FANTASY):

INSERT #array VALUESd.4.BURNING SKY):

INSERT #array VALUES(2.0.SATISFACTION GUARANTEED):

INSERT #array VALUES(2.1.RADIOACTIVE):

INSERT #array VALUES(2.2.MONEY CAN T BUY):

INSERT #array VALUES(2.3.TOGETHER):

INSERT #array VALUES(3.0.GOOD MORNING LITTLE SCHOOLGIRL): INSERT #array VALUES(3.1.HOOCHIE-COOCHIE MAN): INSERT #array VALUES(3.2.MUDDY WATER BLUES): INSERT #array VALUES(3.3.THE HUNTER): SELECT * FROM

(SELECT Free=MAX(CASE band WHEN 0 THEN title ELSE NULL END).

BadCompany=MAX(CASE band WHEN 1 THEN title ELSE NULL END).

TheFirm=MAX(CASE band WHEN 2 THEN title ELSE NULL END).

Solo=MAX(CASE band WHEN 3 THEN title ELSE NULL END)

FROM #array

GROUP BY single) a WHERE Free=BadCompany OR Free=TheFirm OR Free=Solo OR BadCompany-TheFirm OR BadCompany=Solo OR TheFirm=Solo

Free BadCompany TheFirm Solo

.-........ I ................--

THE HUNTER ROCK AND ROLL TOGETHER THE HUNTER

FANTASY

В этом способе предыдущий запрос для создания плоского массива преобразован в производную таблицу, которая затем фильтруется с помощью предложения WHERE (как я говорил в главе 7, вы можете думать о производных таблицах, как о неявных или встраиваемых представлениях). Затем запрос возвращает все записи, в которых название песни (title) одной группы (band) совпадает с названием песни другой группы.

Одна проблема этого способа - он возвращает данные, которые нам не нужны. В данно.м случае это два столбца в середине - нам лищь нужно знать, что группы с номерами нуль и три имеют песни с одинаковыми названиями. Это может означать, что одна группа занимается плагиатом, поэты одной из групп не были столь оригинальными, или что один и тот же ведущий певец пел для обеих групп.

Эффективность - еще одна проблема этого подхода. Производная таблица выбирает каждую запись из таблицы #аггау, перед тем как возвратить ее назад во внешний запрос для сравнения. Так как оптимизатор запросов будет пытаться найти способ объединить эти два запроса в один, наш метод использования

Заключение 251

CASE, возможно, eio запутает. Скорее всего, было бы эффективнее о1-фильг))Овы-вать записи, когда они выбираются, а не после этого. Вот усовершепствова1П1ый код, который это вьиюлияет:

SELECT

Eree=MAX(CASE a.Dand WHEN О THEN a.tule ELSl- NULL END;, Bac;Company=MAX(CASE a,band WHEN 1 THEN a,tite ELSE NULL END;, iheFirm-MAXCCASE a,band WHEN 2 THEN a,title ELSE NULL END), SolO=MAX(CASE a,band WHEN 3 THEN a,title ELSE NULL END) FROM #array a LEFT JOIN #arrdy b ON (a.title=D.title) WHERE NOT (a ,band=b.band AND a.single-b.single) GROUP BY a.single

Free BadCcmpany Thefinm Solo

THE HUNTER NULL NULL IHE HUNIER

В этом способе табличный массив соединяется сам с собой для поиска дуб-лируюпи1хся элементов. Предложение WHERE запроса гарантирует, что .мы ие оши-бе.мся, посчитав сам эле.мент равны.м са.\юму себе же. Поскольку данный под.ход отфильтровывает записи по мерс их обработки, он, скорее всего, будет эффективнее, че.м подход с применением производной таблицы. Однако введение са-люсоединения .может свести на нет увеличение производительности. Будет ли этот подход эффективнее первого, зависит от конкретных обстоятельств и исполь-зуе.мых данных.

За.метьте, что у данного подхода есть побочный эффект: он удаляет ненужные значения из средних столбцов. Сделать это в подходе с производной таб-лице11 было бы сложнее, так как на.м пришлось бы описывать критерий поиска в двух местах: в предложении WHERE и списке SELECT (с по.мощью выраженгиТ CASE).

Заключение

Поскольку в Transact-SQL пе реализована пря.мая поддержка .массивов, они должны э.мулироваться с по.мощью других конструкций. Два наиболее популярных способа эмуляции массивов - хранить их в больших си.мвольных полях или создавать таблицы, столбцы которых будут являться измерениями. Большие строки обычно используются для одно.мерных конструкций, а для много.мерных массивов лучше при.менять табличный метод. Какой бы тип реализации псевдомассивов вы ни выбрали, помните, что хранение повторяющихся значений в записях таблицы - это форма денормализации. Убедитесь, что это вам необходимо, прежде чем переделывать базу данных.

В этой главе вы узнали, как работать с обоими типами псевдомассивов. Вы научились добавлять элементы, удалять их, а также добавлять и очищать целые измерения. Вы узнали, как сделать из массивов плоские перекрестные таблицы и как возвращать элементы массива в виде результирующего множества.

Множества

с помощью рабской и лжшюй лести можно расположить к себе множество разных людей. С помощью подхалимажа можно привлечь их па сбою сторону. Но я пе знаю никого - особенно из числа тех, кого я называю Героя.ми, - кто шел к С1ЮСЙ мечте, требующей самопожертвования, отваги, рещимостн или твердости характера, - и достиг ее с помощью изощренной лести. Эдисон, Джеф(})срсон, Линкольн, Эйнштейн, Сократ, Ко1к})уций, Марк Твен, Элгар По, Леонардо да Винчи, Мартин Лютер Кинг - никто из них не стара/1ся [юпасть и Историю через черный ход. Напротив - они боролись с лизоблюдами и подхалимами. От оставили неизгладимый след в прошлом, ибо имели смелость быть искренними и ноии.мали разницу .между лояльностью

и раболепием.

Трейс Амбрэ

Поскольку реляционная модель основана на множествах кортежей, нет ничего удивительного в том, что SQL Server имеет богатый выбор средств для работы с множествами записей. Понятие множества лежит в основе его работы - сервер обрабатывает сформированные вами запросы путе.м выдачи .множеств - наборов данных. Он хранит множества записей в таблицах и связывает .множества друг с другом с помощью декларативной ссылочной целостности и объединений. Тот факт, что сервер обеспечивает столь мощную поддержку множеств, вполне объясним. Множества - это плоть и кровь реляционных баз данных

В языке ANSI SQL-92 имеются ключевые слова для операций со множествами: UNION, EXCEPT и INTERCEPT. Они служат для вычисления объединения, разности и пересечения множеств, соответственно, (под множествами понимаются наборы записей). Хотя Transact-SQL непосредственно поддерживает только один из этих операторов - UNION, выполнить остальные операции весьма просто, используя несложные технические приемы. SQL представляет собой язык, ориентированный на работу с множествами, он работает с множествами записей лучше всего.

Объединение множеств

Объединение 1\тожеств в Transact-SQI. тривиа.чьно б.латодаря наличию ключевого слова UNION. Pacc.vmxpH.vi при.мер кода, который объединяет два .множества, используя .этот оператор:

CREAlt TABLii #3etl (сэЧ int. cclZ int) CREATE TABLE #5et2 (col3 ч:Е. 004 in:)

iNSER #set] VALUES (1.1)

INSERT #setl VALUES (2.2)

INSERT #setl VALUES (3.3;

INSER #setl VALUES (4.4;

INSERT #seti VALUES (5.5)

INSERT #set2 VALUES (1.1) INSERT #set2 VALUES (2.2) INSERT #set2 VALUES (5.5)

SELECT * FRCM #setl UNION

SELECT * FRCM #set2 coll col2

3 3 2 2 1 1 5 5

За.метьте, что в это.м при.мере и.мена столбцов обеи.х ra6jnm различаются. От операторов SELECT, объединенных с ио.мощью UNION, требуется одинаковое количество столбцов и то, чтобы тины данных соответствующих друг другу столбцов либо совпадали, jn-i6o допускати неявное преобразование одного типа в другой. Са.м по себе оператор SELECT ,\южет быть сколь угодно сложным, но не должен содержать конструкций COMPUTE, ORDER BY hjhi FOR BROWSE. Допускается использовать COMPUTE и ORDER BY 1ю отношешио к ре.зультагу, возвращаемому оператором UNION в цело.м, ио не в отдельных операторах SELECT. И наоборот, конструкции GROUP BY и HAVING .можно использовать в отдельных операторах SELECT, но не по отношению ко все.му набору данных. Это довольно значительное ограничение, но, к счастью, суагествует обходной путь. Рассхкггрим при.мер кода, де-.монстрирующий способ при.меиепия GROUP BY и HAVING к набору данных, фор-н^pyelMo.\Iy оператором UNION:

SELECT coll, Num-COUNTC*)

FROM (SELECT * FROM #setl UNION ALL

SELECT * FROM #set2) s GROUP BY coll HAVING (COUNT(*) > 1)

col 1 Num

254 Глава И. Множества

В этом подходе используется производная таблица в качестве оболочки для набора данных, возвращаемого оператором UNION. Затем этот набор группируется и ограничивается с помощью конструкций GROUP BY и HAVING. В качестве альтернативы можно создать представление на основе оператора UNION, но показанный здесь подход целесообразнее, поскольку он не предусматривает создание специального объекта. Обратите внимание на то, что в коде примера применяется оператор UNION ALL. Оператор UNION удаляет повторяющиеся записи из результата запроса, задействуя его сортировку или хэширование. Разумеется, это может занять какое-то время. Если вы знаете, что результат вашего запроса никогда не содержит повторяющихся значений, или если это обстоятельство не представляет для вас интереса, UNION ALL может оказаться гораздо более быстрым способом соединения таблиц. Он просто объединяет результаты составляющих его операторов SELECT и возвращает получершьгй набор данных - в этом случае никакой сортировки или удаления повторяющихся записей не происходит. В приведенном выше запросе именно это и нужно, поскольку мы хотим использовать раздел HAVING, фильтрующий результат в соответствии с количеством экземпляров каждого из значений столбца coll. Конечно, это было бы невозможно, если бы оператор UNION удалил все повторяющиеся значения, ограничив количество экземпляров каждого из значений одним. Поэтому в промежуточной таблице применяется оператор UNION ALL, после чего агрегирование и удаление дубликатов осуществляется во внешнем операторе SELECT с помощью конструкции GROUP BY.

ВНИМАНИЕ ---

По возможности избегайте совместного использования UNION и UNION ALL. Если повторяющиеся записи удаляются в одних случаях и не удаляются в других, то в результате вы можете получить набор данных, который будет трудно интерпретировать. Когда операторы UNION и UNION ALL применяются совместно, отдельные операторы SELECT, составляющие оператор UNION, перестают быть ассоциативными. К тому же это означат, что принятое в Transact-SQL правило выполнения операторов слева направо будет влиять на результирующий набор данных.

В Transact-SQL и.меется замечательное расширение стандартного синтаксиса оператора UNION, которое позволяет создавать таблицы на лету . Чтобы это сделать, нужно включить раздел INTO tablename в первый оператор SELECT конструкции UNION. Рассмотрим пример:

SELECT * INTO Itempset FROM #setl

UNION ALL

SELECT * FROM #set2

SELECT coll. Num=COUNT(*) FROM #tempset

GROUP BY coll

HAVING (COUNT(*) > 1)

col 1 Num

2 2 5 2

Снача.ш этот код создает таблицу с помощью конструктцп! UNION, затем вьпюлпяет запрос к этой таблице, используя отдельный оператор SELECT. Такая ,\гетодика предпочтительнее, че.м ири.менение производной таблицы, если после выполнения дайной операции и.меется необ.ходи.\гость да.,тьне1ипей обработки набора данны.х, возвращенных операторо.м UNION.

Разность множеств

Для получения набора данных, состояпюго из разности двух .множеств, в ANSI SQL-92 определено ключевое слово EXCEPT. Больщинству разработчиков реали-запий SQL, включая Microsoft, еще только предстоит реализовать это ключевое слово (в Oracle уже и.меется синони.м - ключевое слово MINUS), но поскольку Transact-SQL по своей природе ориентирован иа работу с .множества.ми, вычисление разности .между дву.мя мнoжecтвaиI пе является сложной задачей.

Маиболее очевидиьпг путь определить записи, которые присутствуют в одном .множестве и отсутствуют в друго.м, - применить предикат EXISTS. Вот при.мер кода, возвращающего записи, присутствующие в nepnofi таблице и отсутствующие во второй:

CREATE TABLE #setl (coll int, col2 int) CREATE IABLE #set2 (coil int. col2 inl)

INSERT #setl VALUES (1,1)

INSERT #setl VALUES (2,2)

INSERT #setl VALUES (3,3)

INSERl Isetl VALUES (4,4)

INSERT #setl VALUES (5,5)

INSERT #5eL2 VALUES (1,1) INSERT #set2 VALUES (2,2) INSERT #set2 VALUES (5,5)

SELECT * FROM #5eLl si

WHERE NOT EXISTS(SELECT * EROM #set2 s2 WHERE s2 .coll=sl .col 1 A,\0 s2.col2=sl,coll)

coll col 2

Для поиска записей, присутствующих в таблице #setl и отсутсгвующих в #set2, этот .метод использует связанный подзапрос. Обратите вии.мание - он требует, чтобы каждый столбец в каждой таблице проверялся на совпадение по отдельности. Все это может оказаться весьма громоздки.м, когда речь идет о таблицах с больщи.м количеством столбцов.

В отличие от конструкции ANSI SQL EXCEPT, данное рещение возвращает по-вторяющиеся записи, если они присутствуют в первой таблице. Чтобы избежать ;того, во внспиипг оператор SELECT следует вставить ключевое слово DISTINCT.

Более эффективный путь получения разности двух множеств - при.менение внешнего объединения (outer join). Оно устраняет необходимость связанного подзапроса, поэтому запрос не только быстрее выпол1гяется, по и легче читается:

SELECT si.*

FROM #setl sl LEFT OUTER JOIN #set2 s2

ON (sl.coll=s2,coll AND sl.col2=s2.co12) WHERE S2.C011 IS NULL

coll col 2

Этот подход работает в силу того факта, что левое внепгпее объединенне возвращает пустые значения столбцов из правой таблицы, когда условие объединения не выполнено. Данный запрос офаиичивает результат теми записями, для которых это имеет место. Другими словами, он возвращает записи, которые присутствуют в левой таблипе и отсутствуют в правой. Как и в предыдуще.м примере, данная методика требует сравнения каждого поля в первом множестве с соответствующим полем во втором, что становится уто\гительным, когда полей много.

Существует один тип множеств, для которых ни один из предложе>тых подходов не работает в достаточной степени, - это множества, содержащие повторяющиеся элементы. Реляционная модель Кодда и базовая теория ьнюжестп запрещают повторяющиеся элементы, но язык ANSI/ISO SQL их разрешает, и так же поступает Transact-SQL. Вот почему таблицы иногда называют .мультимножествами - они могут содержать несколько множеств, каждое из которых в отдельности содержит уникальные элементы.

Когда во множествах содержатся повторяющиеся элементы, возгн^кает масса разнообразных проблем. Что делать, если первое множество содержит два экземпляра данной записи, а второе - только один? Набор данных, представляющий разность двух множеств, дол;кен был бы включать г{Овторяющиеся записи из первого множества, для которых нет совпадаюнтх записей во втором множестве. Он не должен был бы исключать запись из результата только потому, что во втором множестве есть совпадающая запись для первого из двух повторяющихся значений.

К сожалению, ни один из представлен1плх методов не может разрепшть эту проблему. Вне зависимости от того, сколько раз данная запись встречается в первом множестве, если она хотя бы раз встречается во втором множестве, она не включается в результат, представляющггй разность множеств. Вот пример запроса, который гарантирует, что запись встречается в одном множестве чаще, че.м в другом (рассматриваемые множества из.менены в целях введения новторяюншхся элементов):

CREATE TABLE #setl (coll 1nt. col2 int) CREATE TABLE #set2 (coll int. col2 int)

INSERT #setl VALUES (l.I)

INSERT #setl VALUES (l.i)

INSERT #setl VALUES (2.2)

INSERT #setl VALUES (3.3)

INSERT bell VALUtS ;4>; INSERT ffsetl VALUES i5,5)

INSERT #set2 VALUES (1.1)

INSERT #5et2 VALUES (2,2)

INSERT #set2 VALUES (5,5)

INSERT #5et2 VALUES (5,5)

SELECT cell, col2 FROM (SELECT coll, col2,

NumKOUNK*),

Niia2-(SELECT COUNK*; TRO:-! *iel2 ss? лНЕ cc I-sii.co12 AND co2=ssl,col2) FROM fsetl ssl GROUP BY coll, co:2) si GROUP BY coll, col2 HAVI NG (ABS (SUM (Num 1) - SUM (Nuiii2); -0,

cell col2

Хотя запись (1,1) встречается в обои.х лиюжествах, этот запрос включает данную запись в разность, поскольку она чаше встречается в перво.м множестве, чем во втором. Аналогичная ситуация и.меет место с записью (5,5) - она встречается в обоих иlOжecтвax, по во втором .\п10жестве чаи;е, че.м в перво.м, и поэтому также включается в результат.

Пересечения множеств

Как и в случае разности .множеств, получить пересечения множеств очень легко с по.мощью предиката EXISTS. Рассмотрим пример:

CREATE TABLE #setl (coll mt, col2 int) CREATE TABLE #set2 (coll int, col2 mt)

INSERT #setl VALUES (1,1)

INSERT #setl VALUES (2,2)

INSERT fseti VALUES (3,3)

INSERT #setl VALUES (4,4)

INSERT #setl VALUES (5,5)

INSERT #set2 VALUES (1,1) INSERT #set2 VALUES (2,2) INSERT #set2 VALUES (5,5)

SELECT * FROM #setl si

WHERE EXISTS(SELEC1 * FRCM #seL2 s2 WHERE s2,coH=sl.col 1 AND s2,col2=5i:col 1) coll col 2

2 2 5 5

258 Глава И. Множества

Как и в предыдущем запросе, вычисляющем разность множеств, данный запрос требует, чтобы каждое поле в первом множестве сравнивалось с соответствующим ему полем во втором. Запрос возвращает каждую запись в первом множестве, поля которой совпадают с полями записи во втором множестве. Поскольку эти записи содержатся в обоих множествах, результат является их пересечением.

Более эффективный способ получить пересечение двух множеств - выполнить их внутреннее объединение (inner join). Оно очень подходит для этой цели, поскольку исключает несовпадающие записи. Рассмотрим пример:

SELECT sl.*

FROM #setl sl INNER JOIN #set2 s2

ON (sl.coll=s2.coll AND sl.col2=s2.col2)

coll col 2

2 2 5 5

С точки зрения синтаксиса этот метод более компактен, работает быстрее и является наиболее распространенным способом получения пересечений множеств с помощью SQL.

Как и в случае с разностями множеств, оба этих метода не в состоянии корректно обрабатывать повторяющиеся значения. Единственная запись во втором множестве может совпадать с двумя или более записями в первом - при этом не существует способа гарантировать, что в обоих множествах запись встречается одинаковое количество раз. Вот запрос, который решает эту проблему:

CREATE TABLE #setl (coTl int, col2 int) CREATE TABLE #set2 (coll int. col2 int)

INSERT #setl VALUES (1.1) INSERT #setl VALUES (1.1) INSERT #setl VALUES (2.2) ,

INSERT #setl VALUES (3.3) INSERT #setl VALUES (4.4) INSERT #setl VALUES (5.5)

INSERT #set2 VALUES (1.1) INSERT #set2 VALUES (2.2) INSERT #set2 VALUES (2.2) INSERT #set2 VALUES (4.4) INSERT #set2 VALUES (5.5)

SELECT coll. col2 FROM (SELECT coll. C0l2.

Numl-COUNT(*).

Num2=CSELECT COUNT(*) FROM #set2 ss2 WHERE coll=ssl.coll AND col2=ssl.col2) FROM #setl ssl GROUP BY coll. col2) Sl GROUP BY coll. col2 HAVING SUMCNuml)-SUM(Num2)