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

Откуда:
Сообщений: 1906
Есть таблица

id	boss_id	name	age
1	NULL	NEW_A	10
2	1	NEW_A	12
3	1	NEW_A	14
4	3	NEW_A	16
5	3	NEW_A	18
6	5	NEW_A	20
7	1	NEW_B	22
8	1	NEW_B	24
9	8	NEW_B	26
10	9	NEW_B	28
11	4	NEW_B	44


select s.id as student, boss.id as boss from Student s, Student boss
where s.boss_id = boss.id --and s.id = 11
order by s.id


возвращает

emp	boss
2	1
3	1
4	3
5	3
6	5
7	1
8	1
9	8
10	9
11	4


Как написать запрос так, чтобы только для одиннадцатого юзера получить всю иерархию подчинения.

то есть

11 -  4
4  -  3
3  -  1


Такая задача решаема sql ?
21 сен 16, 19:50    [19693844]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
msLex
Member

Откуда:
Сообщений: 9290
questioner
Есть таблица

id	boss_id	name	age
1	NULL	NEW_A	10
2	1	NEW_A	12
3	1	NEW_A	14
4	3	NEW_A	16
5	3	NEW_A	18
6	5	NEW_A	20
7	1	NEW_B	22
8	1	NEW_B	24
9	8	NEW_B	26
10	9	NEW_B	28
11	4	NEW_B	44


select s.id as student, boss.id as boss from Student s, Student boss
where s.boss_id = boss.id --and s.id = 11
order by s.id


возвращает

emp	boss
2	1
3	1
4	3
5	3
6	5
7	1
8	1
9	8
10	9
11	4


Как написать запрос так, чтобы только для одиннадцатого юзера получить всю иерархию подчинения.

то есть

11 -  4
4  -  3
3  -  1


Такая задача решаема sql ?

да, recursive cte
21 сен 16, 20:13    [19693894]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
МиккиМауссер
Member

Откуда:
Сообщений: 10
Есть такая штука WITH, обобщенное табличное выражение
Есть пример на msdn.microsoft.com
USE AdventureWorks2012;  
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  
ORDER BY ManagerID;  
GO  

Особое внимание советую обратить на точки с запятой. Их надо ставить до и после запроса, если есть еще какие-нибудь действия перед или после выражения WITH.
22 сен 16, 09:51    [19694936]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
МиккиМауссер,
автор
Особое внимание советую обратить на точки с запятой. Их надо ставить до и после запроса, если есть еще какие-нибудь действия перед или после выражения WITH.

ну после это вы выдумываете, хотя стандарт подразумевает ; после каждой инструкции
22 сен 16, 09:53    [19694948]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
questioner
Member

Откуда:
Сообщений: 1906
МиккиМауссер
Есть такая штука WITH, обобщенное табличное выражение
Есть пример на msdn.microsoft.com

USE AdventureWorks2012;  
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  
ORDER BY ManagerID;  
GO  



Как я понял в вашем примере рекурсия идёт сверху вниз, то есть с самого главного босса и переберется тупо вся таблица.


with cte(worker_id, boss_id, boss_level) as(
	select w1.id, w1.boss_id, 0 from worker w1 where w1.name ='g'
	union all
	select w2.id, w2.boss_id, boss_level+1 from worker w2 inner join cte w3 on w2.id=w3.boss_id)
select * from cte;



вот такая у меня таблица:

id	name	boss_id
1	a	NULL
2	b	1
3	c	1
4	d	1
5	e	3
6	f	3
7	g	6


этот запрос мне выдал всех боссов работника g


Сейчас пытаюсь вкурить как всё дерево форматированно вывести
22 сен 16, 11:24    [19695543]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
МиккиМауссер
Member

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

Вот ваш пример на MSSQL 10
declare @t table (id int , name varchar(10), boss_id  int)

insert into @t 
select 1,	'a',	NULL
union all 
select 2,	'b',	1
union all 
select 3,	'c',	1
union all 
select 4,	'd',	1
union all 
select 5,	'e',	3
union all 
select 6,	'f',	3
union all 
select 7,	'g',	6;

--select * from @t
WITH TT (id)
		AS
		(SELECT id FROM @t WHERE boss_id = 3 
		UNION ALL
		SELECT t.id FROM @t t 
		INNER JOIN TT t2
		ON t2.id = t.boss_id)
select * from @t where id in (SELECT id FROM TT)


Возвращается:
id name boss_id
5 e 3
6 f 3
7 g 6


Получаются все у которых босс имеет id от 3 и до конца рекурсии,
Можно управлять уровнем вложенности , добавив level.
22 сен 16, 12:29    [19696102]     Ответить | Цитировать Сообщить модератору
 Re: Self join  [new]
questioner
Member

Откуда:
Сообщений: 1906
получилось

with cte(worker_id, boss_id, boss_level, full_path) as(
	select w1.id, w1.boss_id, 0, convert(varchar(max),w1.id) from worker w1 where w1.name ='a'
	union all
	select w2.id, w2.boss_id, boss_level+1, w3.full_path + '->' + convert(varchar(max),w2.id)  from worker w2 inner join cte w3 on w2.boss_id=w3.worker_id)
select full_path, boss_id,boss_level from cte
order by full_path
22 сен 16, 12:29    [19696103]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить