Иерархичные данные в MS SQL 2008

Posted: Октябрь 2, 2010 in TSQL

Как правило иерархичные данные реализуются через ID/ParentID. Для примера посмотрим на такую таблицу

CREATE TABLE dbo.Employees(
	ID int NOT NULL PRIMARY KEY CLUSTERED,
	FirstName nvarchar(50) NOT NULL,
	LastName nvarchar(50) NULL,
	ReportsTo int NULL
)

Поле ReportsTo будет указывать на руководителя данного сотрудника. Внесем в таблицу следующую иерархию персонала

  • 1. Вася Иванов
    • 2. Иван Петров
      • 4. Петр Сергеевич
    • 3. Колян
insert into Employees(ID, FirstName, LastName, ReportsTo)
values(1, 'Вася', 'Иванов', null)

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(2, 'Иван', 'Петров', 1);

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(3, 'Колян', null, 1);

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(4, 'Петр', 'Сергеевич', 2);

Теперь, если хотим просмотреть кто кем руководит, создадим простенький запрос

select
	p.ID,
	p.FirstName,
	p.LastName,
	m.FirstName + ' ' + m.LastName as ManagerName
from
	Employees p
	left join
		Employees m
	on
		p.ReportsTo = m.ID

Результатом которого будет

Если мы хотим увидеть иерархичность, то воспользуемся Common Table Expression для указания уровня иерархии

with EmployeeList(ID, FirstName, LastName, ManagerName, Level)
as
(
select
	ID,
	FirstName,
	LastName,
	CAST('' as nvarchar(50)) as ManagerName,
	0 as Level
from
	Employees
where
	ReportsTo is null

union all

select
	p.ID,
	p.FirstName,
	p.LastName,
	CAST(mng.FirstName + ' ' + mng.LastName as nvarchar(50)) as ManagerName,
	mng.Level + 1 as Level
from
	EmployeeList mng
	join
		Employees p
	on
		p.ReportsTo = mng.ID
)
select ID, FirstName, LastName, ManagerName, Level from EmployeeList

Здесь я привожу имя менеджера к varchar(50), потому что мой Management Studio ругался на несовместимость типов. В результате этого запроса получим

Так мы поступали до недавних пор. В 2008 году майкрософт выпустила новую версию своей флагманской СУБД — MS SQL 2008. В ней был реализован новый тип данных — hierarchyid. Давайте посмотрим на него поближе.
Для начала поменяем нашу таблицу добавив поле HID и удалим поле ReportsTo

CREATE TABLE dbo.Employees(
 ID int NOT NULL PRIMARY KEY CLUSTERED,
 HID hierarchyid NOT NULL,
 FirstName nvarchar(50) NOT NULL,
 LastName nvarchar(50) NULL
)

Далее добавим наших сотрудников

insert into Employees(ID, HID, FirstName, LastName)
values(1, hierarchyid::GetRoot(), 'Вася', 'Иванов')

declare @ID hierarchyid

select @ID = MAX(HID)
from Employees
where hid.GetAncestor(1) = hierarchyid::GetRoot()

insert into Employees(ID, HID, FirstName, LastName)
values(2, hierarchyid::GetRoot().GetDescendant(null, null), 'Иван', 'Петров');

insert into Employees(ID, HID, FirstName, LastName)
values(3, hierarchyid::GetRoot().GetDescendant(null, null), 'Колян', null);

declare @auxID hierarchyid
select @auxID = (select HID from Employees where ID = 2);

select @ID = MAX(HID)
from Employees
where hid.GetAncestor(1) = @auxID

insert into Employees(ID, HID, FirstName, LastName)
values(4, @auxID.GetDescendant(null, null), 'Петр', 'Сергеевич');

select HID.ToString(), HID.GetLevel(), * from Employees

Сначала мы добавили корень.
hid.GetAncestor(1) = hierarchyid::GetRoot() — выбирает все записи, прямым предком которых является корень
parent.GetDescendant (null, null) — возвращает одного потомка данного родителя
child.GetAncestor(n) — возвращает идентификатор hierarchyid, представляющий n-го предка данного элемента

Запросим данные

select HID.ToString(), HID.GetLevel(), * from Employees

В результате чего увидим

Как мы видим, запрос, по сравнению с Common Table Expression, сильно упростился. Но этому предшествовала кропотливая работе по вставке. При работе с данными hierarchyid есть куча методов. Нет смысла их приводить здесь. Это как раз тот случай, для которого создавался MSDN. Стоит отметить, что это довольно интересное решение, но уж больно громоздкое и непереносимое на другие СУБД. Так что стоит несколько раз подумать, прежде чем его использовать.

Реклама

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s