Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Связанное XML поле  [new]
sniffysko
Member

Откуда:
Сообщений: 11
Имеется 3 таблицы. Сотрудники, подразделения, должности:

DECLARE @users TABLE (
[id] bigint,
[name] varchar(100),
[pos_id] bigint
)

INSERT INTO @users VALUES
(1, 'Иванов Иван', '44'),
(2, 'Петров Петр', '55')

DECLARE @pos TABLE (
[id] bigint,
[name] varchar(100),
data xml
)

INSERT INTO @pos VALUES (1, 'Колбасник',
'<subdivs>
<subdiv>
<type>position</type>
<name>Колбасник</name>
<id>0</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>Колбасный цех</name>
<id>4</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>Отдел производства</name>
<id>3</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>ООО Большая колбаса</name>
<id>1</id>
</subdiv>
<subdivs>'),
(2, 'Сосисочник',
'<subdivs>
<subdiv>
<type>position</type>
<name>Сосисочник</name>
<id></id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>Отдел сосисок</name>
<id>5</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>Колбасный цех</name>
<id>4</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>Отдел производства</name>
<id>3</id>
</subdiv>
<subdiv>
<type>subdiv</type>
<name>ООО Большая колбаса</name>
<id>1</id>
</subdiv>
<subdivs>')

DECLARE @subdivs TABLE (
[id] bigint,
[name] varchar(100),
[parent_id] bigint
)

INSERT INTO @subdivs VALUES
(1, 'ООО Большая колбаса', NULL),
(2, 'Отдел маркетинга', 1),
(3, 'Отдел производства', 1),
(4, 'Колбасный цех', 3),
(5, 'Отдел сосисок', 4)


В самом деле структура значительно сложнее и сложенность там больше 5-6 уровней.
Что нужно: выудить из запроса отдел, в котором работает сотрудник. Т.е. это уровень вложенности МaxГлубина - 1.
У меня примерно такой запрос, но что-то мне подсказывает, что это, возможно, не самый оптимальный вариант:

SELECT
usr.*,
ps.data.query('for $n in (/subdivs/subdiv[position() = (count(/subdivs/subdiv) - 1)]) return ($n/id)[1]').value('.', 'bigint') AS sector_id
FROM @users AS usr
LEFT JOIN @pos AS ps ON ps.id = usr.pos_id

По поводу структуры БД прошу не ругать. Все принимается как данность. :о(
30 июн 16, 16:09    [19355642]     Ответить | Цитировать Сообщить модератору
 Re: Связанное XML поле  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
sniffysko, забавно... невалидные данные и нерабочий запрос... что оптимизировать? Что такое МaxГлубина? глубины замечено не было...
30 июн 16, 16:40    [19355886]     Ответить | Цитировать Сообщить модератору
 Re: Связанное XML поле  [new]
sniffysko
Member

Откуда:
Сообщений: 11
Запрос сокращал оставил ошибку. :о(
МaxГлубина -- Если взять список подразделений из поля @pos.data? то это количество <subdiv> в <subdivs>
30 июн 16, 16:46    [19355948]     Ответить | Цитировать Сообщить модератору
 Re: Связанное XML поле  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
sniffysko, в общем...
+
SELECT * 
FROM @users usr join @pos pos on usr.id = pos.id
		cross apply
		(
			select pos.data.value('max(//subdivs/subdiv[type = "subdiv"]/id)', 'int') as max_id, pos.data.value('count(//subdivs/subdiv[type = "subdiv"]/id)', 'int') as count_id
		) sd
30 июн 16, 16:58    [19356020]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить