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

Откуда:
Сообщений: 7
Имеем две таблицы, описывающие зависимость IT сервисов от других других IT сервисов.
table1 содержит описания сервисов.
table2 описывает зависимость сервисов в дереве.

Пример данных
table1
SERVICEIDNAME
65Active Directory
66srv-dc01
68srv-dc02_vm
54esx_cluster
56esx_host1


table2
LINKIDSERVICEUPIDSERVICEDOWNID
16566
26568
36854
45456


На словах смысл такой:
Сервис "Active Directory" зависит от двух контроллеров srv-dc01 и srv-dc02, причем srv-dc02 виртуальный.
srv-dc01 это физический сервер, который ни от чего не зависит.
srv-dc02 виртуалка, которая зависит от кластера vmware, который, в свою очередь, зависит от хоста esx_host1.

Нужно сделать запрос, который покажет на каких физических серверах находится сервис "Active Directory", опустив все промежуточные зависимости.
То есть результат должен выдать следующую таблицу:
UpServiceIDDownServiceID
6566
6556
21 июн 13, 15:26    [14466496]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шилеев Константин
То есть результат должен выдать следующую таблицу:
UpServiceIDDownServiceID
6566
6556

Здесь нет никакого дерева - здесь только зависимость 1го уровня.
Которая делается обыкновенным join
21 июн 13, 15:30    [14466531]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
Glory,

Это же данные только для примера.
На самом деле уровней вложенности может быть произвольное количество, как и элементов первого уровня.
Есть еще такие сервисы как Exchange, Citrix и пр., просто я не стал их приводить, для упрощения примера.
Ну и, если все так просто, может покажете пример запроса?
21 июн 13, 15:42    [14466649]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шилеев Константин
Ну и, если все так просто, может покажете пример запроса?

Стандартный CTE из хелпа не подходит что ли ?

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
21 июн 13, 15:47    [14466680]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
Glory,

нет, этот запрос возвращает те самые данные, которые уже содержатся в table2.
В вашем запросе поле ManagerID равнозначно полю SERVICEUPID примера, а поле EmployeeID - полю SERVICEDOWNID.

Мне же нужно выбрать элементы верхнего уровня (т.е. у которых нет вышестоящих элементов) и вывести соответствующие им самые нижние элементы (у которых нет нижележащих элементов).
Промежуточные элементы в результат попадать не должны.
То есть в моем примере промежуточными являются srv-dc02_vm (ID 68) и esx_cluster (ID 54).
21 июн 13, 17:53    [14467519]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шилеев Константин
Промежуточные элементы в результат попадать не должны.
То есть в моем примере промежуточными являются srv-dc02_vm (ID 68) и esx_cluster (ID 54).

Ну так офильтруйте их
21 июн 13, 17:59    [14467555]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
Glory,

Запрос, который вы привели, возвращает следующую таблицу
ManagerIDEmployeeIDTitleEmployeeLevel
NULL1Chief Executive Officer0
1273Vice President of Sales1
27316Marketing Manager2
273274North American Sales Manager2
273285Pacific Sales Manager2
285286Sales Representative3
274275Sales Representative3
274276Sales Representative3
1623Marketing Specialist3


Буду очень благодарен если вы подскажите, как мне из нее отфильтровать данные, чтобы на выходе получилось

Chief Executive OfficerSales Representative (286)
Chief Executive OfficerSales Representative (275)
Chief Executive OfficerSales Representative (276)
Chief Executive OfficerMarketing Specialist (23)


То есть взять самого верхнего босса и вывести всех его конечных подчиненных, минуя менеджеров среднего уровня.
Также нужно иметь ввиду что на самом деле в реальной таблице верхних боссов будет несколько, каждый со своими подчиненными. А некоторые менеджеры среднего звена будут подчинятся сразу нескольким верхним боссам.
PS: Надеюсь я не окончательно запутал пример ))
21 июн 13, 18:18    [14467650]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
serpentariy
Member

Откуда:
Сообщений: 265
Шилеев Константин,

Верхний босс - MIN(EmployeeLevel), все его конечные подчиненные, минуя менеджеров среднего уровня - MAX(EmployeeLevel)
21 июн 13, 18:39    [14467719]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
serpentariy,

А если верхних боссов несколько? Каждый со своими подчиненными? У них же EmployeeLevel будет одинаков.
Да и ветка может кончатся как на 2-м уровне, так и на 9-м.

Просто раньше мои познания в SQL дальше Select-ов с Join-ми не распространялись (не было необходимости, я вообще Microsoft администратор, AD там, Exchange).
А вот сейчас встала задача сделать такой отчет.
На самом деле данные выгребаются из таблиц Zabbix, есть там такое дерево "услуги IT".
21 июн 13, 18:53    [14467760]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
serpentariy,
и все равно, даже используя MIN и MAX как именно вернуть требуемую таблицу?
22 июн 13, 18:21    [14469519]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
Шилеев Константин
Member

Откуда:
Сообщений: 7
Вроде получилось.
Выглядит, наверное, очень коряво, но работает. Пришлось оформить в виде процедуры.

USE [zbx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_parents_with_leaves]
AS

BEGIN
	SET NOCOUNT ON;
	
	DECLARE @NextParrent int
	
	DECLARE CurParrentID CURSOR
	FOR SELECT [ServiceUpID]
	FROM [zbx].[dbo].[it_services_tree]
	WHERE [ServiceUpID] NOT IN
	(
	SELECT [ServiceDownID] FROM [zbx].[dbo].[it_services_tree]
	)
	GROUP BY [ServiceUpID]
	
	CREATE TABLE #t (ServiceUpID INT, ServiceDownID int);
	
	OPEN CurParrentID
	FETCH NEXT FROM CurParrentID
	INTO @NextParrent
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		WITH result(ServiceUpID, ServiceDownID) AS 
		(
		SELECT ServiceUpID, ServiceDownID
		FROM dbo.it_services_tree
		WHERE ServiceUpID = @NextParrent
		UNION ALL
		SELECT t.ServiceUpID, t.ServiceDownID
		FROM dbo.it_services_tree AS t
			INNER JOIN result AS r
			ON t.ServiceUpID = r.ServiceDownID 
		)
		INSERT #t
			SELECT @NextParrent AS RootServiceID, ServiceDownID AS LeavesID
			FROM result
			WHERE ServiceDownID NOT IN
				(
				SELECT ServiceUpID FROM dbo.it_services_tree
				)
			GROUP BY ServiceUpID, ServiceDownID
		
		FETCH NEXT FROM CurParrentID
		INTO @NextParrent
	END
	CLOSE CurParrentID
	DEALLOCATE CurParrentID
	
	SELECT ServiceUpID, ServiceDownID FROM #t
	
END
22 июн 13, 22:28    [14469792]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
popina
Member

Откуда:
Сообщений: 49
Получилось что-то типа этого

with servlist(serviceid, servup, serv) as
(
	select serviceid, cast(null as smallint), serviceid  from table1 as t1
	where t1.name = 'AD'--тут надо поставить нужное условие
	union all
	select t2.servicedownid, t2.serviceupid, srv.serv from table2 as t2, servlist as srv
	where srv.serviceid = t2.serviceupid
)
select 
	tbl2.name as servup,
	tbl1.name as servdown
from 
	servlist as srv
inner join table1 as tbl1 on
	tbl1.serviceid = srv.serviceid
inner join table1 as tbl2 on
	tbl2.serviceid = srv.serv
where
	(select count(1) from servlist s1 where s1.servup = srv.serviceid) = 0
23 июн 13, 01:47    [14469987]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
declare @table1 table (SERVICEID int,NAME varchar(60))
insert into @table1 values
(65, 'Active Directory') 
,(66, 'srv-dc01' )
,(68, 'srv-dc02_vm')
,(54, 'esx_cluster') 
,(56, 'esx_host1') 

declare @table2 table (LINKID int, SERVICEUPID int,SERVICEDOWNID int)
insert into @table2 values
 (1, 65, 66) 
,(2, 65, 68) 
,(3, 68, 54) 
,(4, 54, 56) 
 
select * from  @table1
select * from  @table2

;with root (rootid,SERVICEUPID,level) AS(
select SERVICEUPID as rootid, SERVICEDOWNID as SERVICEUPID,0 as level from @table2 t1
where not exists(select * from @table2 t2 where t2.SERVICEDOWNID = t1.SERVICEUPID)
union  all
select t1.rootid, t2.SERVICEDOWNID as SERVICEUPID,t1.level+1 as level 
from root t1
join @table2 t2 on t2.SERVICEUPID = t1.SERVICEUPID)
select * 
from root t1
where not exists(select * from @table2 t2 where t2.SERVICEUPID = t1.SERVICEUPID)
24 июн 13, 08:42    [14471747]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом таблицы, описывающей древовидные данные  [new]
serpentariy
Member

Откуда:
Сообщений: 265
declare @table table (LINKID int, SERVICEUPID int,SERVICEDOWNID int)
insert into @table values  (1, 65, 66),(2, 65, 68),(3, 68, 54),(4, 54, 56) 

;WITH cte(SERVICEUPID, SERVICEDOWNID,q) AS 
(
    SELECT DISTINCT t2.SERVICEUPID, t1.SERVICEUPID, t1.SERVICEUPID
    FROM @table t1 left join @table t2 on t1.SERVICEUPID=t2.SERVICEDOWNID
    where t2.LINKID is null
    UNION ALL
    SELECT t.SERVICEUPID, t.SERVICEDOWNID,c.q
    FROM @table AS t JOIN cte AS c ON t.SERVICEUPID = c.SERVICEDOWNID
)
SELECT c1.q AS UpServiceId,c1.SERVICEDOWNID AS DownServiceId
FROM cte c1 left join cte c2 on c1.SERVICEDOWNID=c2.SERVICEUPID
where c2.SERVICEDOWNID is null
24 июн 13, 10:31    [14472289]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить