FB9p0WzV

1use my_dev
2GO
3
4if not exists (select * from sysobjects where name ='employees' and xtype='U') 
5BEGIN
6CREATE TABLE employees (
7  ID INT NOT NULL PRIMARY KEY IDENTITY (1,1),
8  ManagerID INT NULL FOREIGN KEY REFERENCES employees(ID),
9  Salary INT NOT NULL
10  )
11END
12GO
13
14if NOT EXISTS (SELECT 1 FROM employees)
15BEGIN
16	INSERT INTO employees (ManagerID, Salary)
17		VALUES	(NULL, 100)
18		,		(1, 200)
19		,		(2, 150)
20END
21
22
23; WITH __cte AS 
24(
25		-- ancor member expression
26		-- отправная точка
27		-- берем гендиректора
28		SELECT	
29				
30				e.Salary					
31		,		e.ID						
32		,		e.ManagerID					
33		,		e.Salary	AS SalaryDiff
34		,		0			AS Stack
35		FROM	employees e
36		WHERE e.ManagerID IS NULL 
37	UNION ALL
38		-- recursive member expression
39		-- и от гендиректора спускаемся по подчиненным
40		-- то есть перебираем каждую запись рекурсивно
41		SELECT	e.Salary					
42		,		e.ID						
43		,		e.ManagerID					
44		,		 e.Salary - c.SalaryDiff AS SalaryDiff 
45		,		c.Stack + 1			AS Stack
46		FROM	employees e
47		JOIN	__cte c
48			ON c.Id = e.ManagerID
49)
50SELECT * 
51from __cte c
52--WHERE c.Diff > 0