В этом подходе используется пропзволпая таблица и подзапрос для вычисления количества записей, которые встречаются в каждом множестве для каждой иарь! значений. Далее возвращаемый набор записей ограничивается те.ми из ппх, которые встречаются в обоих .хиюжествах одинаковое число раз. В данно.м случае запись (1,1) исключается, поскольку в перво.м .множестве она встречается два раза, а во второ.м - только один. Анатогичны.м образо.\г исключается и (2,2), так как она один раз встречается в первом множестве п два раза - во второ.м.

Вычислетиге нересечення .множеств, основанное на подсчете количества записей, в большипстве случаев представляет чисто акаде.\И1ческий интерес. Вас .может не интересовать, что эти количества разные, - для вас может и.меть значение только тот факт, что два множества содержат общий эле.мент. В тако.м случае первые два предложенных .метода решают требуемую задачу, используя .vrnHH.My.vr кода.

Подмножества

Конечно же, са.\ш1й простой способ выбрать часть некоторого множества (то есть гюд.\н10жество) - при.менить оператор SELECT и указать условия отбора в разделе WHERE. Это наиболее пря.молпиейный подход, и и.менпо его чаще всего и выбирают.

А что, если нам нужно нечто такое, что было бы слишком сложно (по крайней .мере, на первый взгляд) указать в разделе WHERE? Рассмотри.м задачу о выборке первых п Bannceii множества. Как ее лучше решить?

Существует несколько способов реше1П1я проблемы. Некоторые из них представлены в ЭТОЙ! книге (с.\г, например, раздел Получение п первых записей в главе 8), и здесь я не стану в них углубляться. В контексте множеств и подмножеств полезно упо.чшиуть расширение ТОР п оператора SELECT, хотя оно подробно рассматривается в других разделах этой книги. Конечно, наиболее прямолинейный способ вывести иачатьпую часть множества - это использовать в запросе конструкцию ТОР п, как показано в следующем при.мере:

CREATE TABLE #1996 P0P ESTIMATE (Region char(7), State char(2). Population int)

INSERT #1996 P0P ESTIMATE VALUES CWesf. CA.31878234) INSERT #1996 PCP ESTIMATE VALUES (South, TX.19128261) INSERT #1996 P0P ESTIMATE VALUES (North. N/,18184774) INSERT #1996 P0P ESTIMATE VALUES (South, FL,14399985) INSERT #1996 P0P ESTIMATE VALUES (North, NJ, 7987933) INSERT #i996 P0P ESTIMATE VALUES (East, NC, 7322870) INSERT #1996 P0P ESTIMATE VALUES (West, WA, 5532939) INSERT #1996 P0P ESTIMATE VALUES (Central,MO, 5358692) INSERT #i996 PCP ESTIMATE VALUES (East, MD, 5071604) INSERT #1996 P0P ESTIMATE VALUES (Central,OK, 3300902) SELECT TCP 3 State, Region, Population

FROM #1996 P0P ESTIMATE ORDER BY Population DESC

State Region Population

CA West 31878234

TX South 19128261

NY North 18184774

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

SET ROWCOUNT 3

SELECT State. Region. Population

FROM #1996 P0P ESTIMATE

ORDER BY Population DESC

SET ROWCOUNT 0 Reset ROWCOUNT

Одно важное преимущество конструкции TOP п по сравнению с командой SET ROWCOUNT заключается в ее способности корректно обрабатывать логически связанные записи. Настройка WITH TIES позволяет конструкции ТОР п включить эти записи в результат, если в запросе имеется условие сортировки в разделе ORDER BY. Рассмотрим такой вариант предыдущего запроса:

SELECT ТОР 5 State. Region, Population=Popu1ation/1000000

FROM #1996 P0P ESTIMATE

ORDER BY Population/1000000 DESC

State Region Population

CA West 31

TX South 19

NY North 18

FL South 14

NJ North 7

Этот пример выводит'пять крупнейших по численности населения штатов США. Население указывается в миллионах жителей, дробная часть отбрасывается. Если не указать настройку WITH TIES, запрос не сможет учесть тот факт, что на самом деле пятое место в этом списке делят две записи. В штатах Нью-Джерси и Северная Каролина на 1996 г. проживало по 7 миллионов человек. Вот запрос, использующий настройку WITH TIES, и результат, который он возвращает:

SELECT TOP 5 WITH TIES State. Region. Population=Population/1000000

FROM #1996 P0P ESTIMATE

ORDER BY Population/1000000 DESC

State Region Population

Поскольку раздел ORDER BY поддерживает как иозрастающиГк так и убыиаю-шнй порядок сортировки, конструкцию ТОР п \южио использовать также для вывода последних aaimceii лиюжества, как в показанном ниже примере:

SELECT ТОР 5 WITH ПЕЕ State. Region. PopuatonPcpuaticn/iCOOOOO FROM #:995 P0P ES[IMATE ORDER BY Population/ICOGOOC

State Region Population

OK Centnal 3 WA West 5

MO Centndi 6 MD East £

NJ Nonth 7 NC East 7

Если нужно изменить порядок сортировки результата, возвращенного конструкцией ТОР п, (например, вы хотели бы получить показанный выше результат в убывающем порядке), его .\южно и(5.\1естить в производную таблицу и упорядочить с помощью отдельного условтгя ORDER BY. Вот при.мер такого решения:

SELECT * FRCM (SELECT TOP 5 WITH TIES State.

Region, Population-Population/lOOOOOO

FROM #1996 P0P ESTIMATE

ORDER BY Population/1000000) p ORDER BY Population DESC

State Region Population

NJ Nonth 7

NC East 7

WA West 5

MO Centnal 5

MD East 5

OK Centnal 3

Вывод каждой n-й записи

По.\П1МО выборки записей от naniuia и конца .хнюжества, .\южет возникнуть необходи.мость получать их в зависилюсти от позиции. Например, может потребоваться вывести элементы только с четпы.\ги или печетны.мп но.мера.\ш либо, скажем, каждый третий или каждыГ! пятьй!. Это такая же фунда.\1ентальная задача, как выборка интервала из последовательпостп или cepirn. Примеры в главе 9, Серии и последовательности , показывают, как получить интервалы, размер которых превышает одну запись и к которы.м .могут быть при.менены другие, более сложные критертик Пока же расс\ютрим запрос, показывающий, как получить все четные элементы .чгножества:

CREATE TABLE #setl (kl int identity)

INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES

INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES INSERT #setl DEFAULT VALUES

SELECT sl.kl

FROM #setl sl JOIN #5etl s2 ON Csl.kl >= 52.kl)

GROUP BY sl.kl

HAVING (COUNT(*) % 2) = 0

2 4 6 8 10

В этом подходе применяется уже знакомый прием, представленный ранее в книге - для сравнения таблицы с самой собой используется внутреннее объединение в сочетании с конструкцией GROUP BY. Затем, чтобы ограничить результирующий набор данных четными записями, применяется оператор остатка от деления (%). Разумеется, для получения нечетных записей достаточно было бы поменять =0 на =1, как показано ниже;

SELECT Sl.kl

FROM #setl Sl JOIN #setl s2 ON (sl.kl >= s2.kl)

GROUP BY Sl.kl

HAVING (COUNT(*) % 2) - 1

Заключение

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

Иерархии

Если вы считаете, что образование слитком дорого, --

выбирайте невежество.

Дерек Бок, бывший президент Гарвардском университета

Иерархия - это особый тип организации данных, в которо.м данные представлены в виде узлов (nodes), связанных друг с другом через однонаправленные зависи.мости, известные как ребра (edges). Эти узлы располагаются иа множестве уровней и в первом приближении напоминают дерево. И действительно - понятия иерархия и дерево часто используются как синони.мы. Следует отметить, что в Transact-SQL и.меется весьма скудная поддержка иерархий и деревьев. Другие продукты - такие как Oracle - реализуют ее гораздо полнее, но Transact-SQL поче.му-то отстает в этой области. Тем не менее это не такой уж большой недостаток, как могло бы показаться па первый взгляд, - сушествует цельй'г ряд несложных технических приемов, с по.мошью которых представление и обработка иерархий в Transact-SQL оказывается довольно простой задачей.

Существует .множество распространенных задач пpoфaм.vп[poвaния, связанных с навигацией в древовидных структурах и их обработкой. Первая задача, которая сразу приходит в голову, - это представление организационной диагра.м.мы предприятия, основанной на штатно.м расписании. Каждый работник занимает в нем одну запись, а каждая запись и.меет указатель на начальника этого работника, который са.м может представлять другую запись в штатно.м расписании. Обычно такие типы иерархий реа^тизуются с помощью единственной таблицы.

В противоположность этому задача о спецификации изделия (Bill of Materials - BOM), которая включает определение всех отдельных частей, составляющих предмет, обычно решается с по.мопгью двух таблиц. В отличие от организационной диаграммы, узлы, представляющие составные части, могут .многократно содержаться внутри дерева. Напри.мер, один и тот же эле.мент .может быть составной частью нескольких пред.метов в спецификации ВОМ. При.менение второй таблицы сохраняет нор.ма/тизацию базы данных и позволяет составным частя.м многократно присутствовать в иерархии.

264 Глава 12, Иерархии

Простые иерархии

Если вас интересуют только иерархии с одним уровнем вложенности, то код SQL, нужный для их формирования, довольно тривиален. Вот пример, который выдает одноуровневую организационную диаграмму предприятия:

CREATE TABLE Staff (employee int PRIMARY KEY. employee name varchar(lO). supervisor int NULL REFERENCES staff (employee))

INSERT staff VALUES (1.GROUCHO.1) INSERT staff VALUES (2,CHICO.1) INSERT staff VALUES (3.HARPO.2) INSERT staff VALUES (4,ZEPPO.2) INSERT staff VALUES (5.MCE.1) INSERT staff VALUES (6.LARRY.5) INSERT staff VALUES (7.CURLY.5) INSERT staff VALUES (B.SHEMP,5) INSERT staff VALUES (9.JOE.8) INSERT staff VALUES (10.CURLY JOE.9)

SELECT t.employee name. superv1ses=supervises, s.employee name FROM staff s INNER JOIN staff t ON (s.supervisor.employee) WHERE s.supervisoros.employee ORDER BY s.employee, s.supervisor

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

Многоуровневые иерархии

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

бирая не только начальн1н<а каждого раоопшка, но н пачапьннка его начальника - и далее вьнпе но иерархии вплоть до председателя совета директоров. Вот запрос, который ,зто делает:

SELECT chartdepln=l, employee=o2.employee. superv1scr=ol.employee INTO torg&.an

FROM staff ol INNER JOIN staff o2 ON (cl.eiiployee=o2.supervisor) INSERT INTO #0g chart

SELECT DISTINCT ol.chartdeptn+1. o2.employee, ol.supervisor

FROM #crg chart ol INNER JOIN #org chart o2 ON (ol.c-iiiployeo=c2.supervisor)

WHERE cl.chartdepth=(SELECT MAX(chartdepth) FROM #org chart)

INSERT INTO #org crart

SELECT DISIINCI cl.chartdeptn 1. o2.eTiployee. ol.supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol.emp;oyee=o2.supervisor)

WHERE ol.chartdepth-(SELECT MAX(chartdepth) FROM #org chart)

INSERT INTO #org ctiart

SELECT DISTINCT ol,chartdepth+l, o2,employее, ol,supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol,employee=o2.supervisor)

WHERE ol.chartdepth=(SELECT MAX(chartoepth) FROM #org chart)

INSERl INTO #org cnart

SELECT DISTINCT ol,chartdepth+l, o2,employee, o!,supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol,employee=o2.supervisor)

WHERE ol,chartdepth=(SELECT MAX(chartdepth) FROM #org chart)

INSERT INTO #org chart

SELECT DISTINCT ol.chartdepth+l, o2,employee, ol,supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol,emplcyee=o2.supervisor)

WHERE ol.clnartdeptli=(SELECT MAX(clnartdeptl-,) FROM #org chart)

INSERT INTO #org cliart

SELECT DISTINCT ol .cliartdepti-.+l, o2.employee, ol,supervisor

FROM #org cliart ol INNER JOIN #org cliart o2 ON (ol,employee o2, supervi sor)

WHERE ol,chartdeptli=(SELECT MAX(cliartdeptli) FROM #org ciiart)

INSERT INTO #org cliart

SELECT DISTINCT ol ,cliartdepth+l, o2,employee, ol .supervisor

FROM #org cnart ol INNER JOIN #org chart o2 ON (ol.employee=o2,supervisor)

WHERE ol,clnartdepth=(SELECT MAX(ctiartdeptli) FROM #org chart)

SELECT s.employee name, supervises=supervises, e,employee riame FROM #org chart о INNER JOIN staff s ON (o,supervisors,employee) INNER JOIN staff e ON (o,employee=e,employee) WHERE o,supervisor<>o,employee

GROUP BY 0,supervisor, o,employee, s,employee r.ame, e,employee name ORDER BY 0.supervisor, o,employee, s,employee name, e,employee name

employee name supervises employee name

GROUCHO supervises CHICO

GROUCHO supervises HARPO

GROUCHO supervises ZEPPO

GROUCHO supervises МОЕ

GROUCHO supervises LARRY

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

SELECT chartdepth=l, етр1оуее=о2.employee, supervisor=ol.employee

INTO #org chart

FROM staff ol INNER JOIN staff o2 ON (ol.employee=o2.supervisor)

WHILE (№rowcount > 0) BEGIN

INSERT #org chart (chartdepth. employee, supervisor)

SELECT DISTINCT Ol.chartdepth+l. o2.employee, ol.supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol.employee=o2.supervisor)

WHERE ol.chartdepth=(SELECT MAX(chartdepth) FROM #org chart)

AND ol.supervisorool.employee

SELECT s.employee name. superv1ses=supervises. e.employee name FROM #org chart о INNER JOIN staff s ON (o.supervisors.employee) INNER JOIN staff e ON (o.employee=e.employее) WHERE o.supervisoroo.employee

GROUP BY 0.supervisor, o.employee, s.employee name. e.employee name ORDER BY 0.supervisor, o.employee, s.employee name. e.emp1oyee name

МОЕ

МОЕ

SHEMP

SHEMP

supervises JOc

supervises CuRL) JOE

supervises JOE

supervises CURLY JOE

supervises CURLY JOE

Здесь применяется цикл WHILE для повторения оператора INSERT столько раз, сколько необходимо для обработки всех уровней иерархии. Этот подход работает при любом числе уровней, при это.м пе нужно знать заранее их количество.

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

SELECT seq=IDENTlTY(int). chartdept.hl. emplоуее=о2.employee. supervlsor=ol.employee INTO #org chart

FROM staff ol INNER JOIN staff o2 ON (ol.employee=o2.supervisor)

WHILE (giarowcount > 0) BEGIN

INSERT #org chart (chartdepth, employee, supervisor)

SELECT DISTINCT ol.chartdepth+l, o2.employee, ol.supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol.employee=o2.supervisor)

WHERE oI,chartdepth=(SELECT MAX(chartdepth) FROM #org chart)

AND ol,supervisorool,employee

SELECT s,employee name, supervises-supervlses, e.employee name FROM #org chart о INNER JOIN staff s ON (o,supervisors,employee) INNER JOIN staff e ON (o.employee-e,employее) WHERE o,superv1sor<>o,employee ORDER BY seq

GROUCHO supervises CURLY JOE

МОЕ supervises CURLY JOE

GROUCHO supervises CURLY JOE

Для создания в рабочей таблице автоинкрементного поля данный подход использует функцию IDENTITY() в сочетании с оператором SELECT...INTO. При выводе результата это поле используется для сортировки возвращаемого набора данных.

Отображение уровней иерархии

При работе с иерархическими данными часто бывает необходимо отобразить уровень иерархии с по.мощью табуляции. Поскольку в предыдуще.м примере задача обхода всех уровней иерархии уже рен1ена, представление результата в табулированной форме не вызывает особых трудностей. Рассмотрим следующий вариант предыдущего запроса, который вводит в результат табуляцию, соответствующую иерархическому уровню: SELECT seq=IDENTITY(int).

chartdepth=CASE WHEN o2.employee-o2.supervisor THEN 0 ELSE 1 END,

employee=o2.employee.

supervi sor=ol.employee INTO #org chart

FROM staff ol INNER JOIN staff o2 ON {ol.employee=o2.supervisor)

WHILE (@@rowcount > 0) BEGIN

INSERT #org chart (chartdepth. employee, supervisor)

SELECT DISTINCT ol.chartdepth+l. o2.employee, ol.supervisor

FROM #org chart ol INNER JOIN #org chart o2 ON (ol.employee=o2.supervisor)

WHERE ol.chartdepth=(SELECT MAX(chartdepth) FROM #org chart)

AND ol.employeeool.supervisor

SELECT 0rgChart=REPLICATE(CHAR(9).chartdepth)+s.emp1oyee name FROM (SELECT employee.

seq=MIN(seq).

ch*a rtdepth=MAX (chartdepth) FROM #org chart

GROUP BY employee) о INNER JOIN staff s ON (o.employee=5.empl oyee) ORDER BY o.seq

OrgChart

GROUCHO

CHICO МОЕ

HARPO ZEPPO

LARRY CURLY SHEMP

CURLY JOE

Этот пример использует функцию REPLICATE() для генерации строки, состоящей из такого количества символов табуляции, которое соответствует значению