gt1vzHyt

1use my_dev
2GO
3
4
5if exists (select * from sysobjects where name ='employees' and xtype='U') 
6	drop table employees
7GO
8
9CREATE TABLE employees (
10  ID INT NOT NULL PRIMARY KEY IDENTITY (1,1),
11  ManagerID INT NULL FOREIGN KEY REFERENCES employees(ID),
12  Salary INT NOT NULL
13  )
14GO
15
16
17if NOT EXISTS (SELECT 1 FROM employees)
18BEGIN
19
20	INSERT INTO employees (ManagerID, Salary)
21		VALUES	(NULL, 100) -- 1
22		,		(1, 200)    -- 2
23		,		(2, 150)    -- 3
24		,		(1, 150)    -- 4
25		,		(3, 50)     -- 5 
26		,		(3, 175)    -- 6
27
28END
29
30
31; WITH __cte AS 
32(
33		-- ancor member expression
34		SELECT	e.Salary					
35		,		e.ID						
36		,		e.ManagerID				
37		-- порядок полей очень важен
38		,		e.Salary					AS RootSalary	
39		,		0							AS RootSalaryDiff
40		,		e.ID						AS RootID
41		,		0							AS Stack
42		FROM	employees e
43	UNION ALL
44		-- recursive member expression
45		SELECT	e.Salary					
46		,		e.ID						
47		,		e.ManagerID				
48		-- порядок полей очень важен
49		,		c.RootSalary				AS RootSalary
50		,		c.RootSalary - e.Salary		AS RootSalaryDiff 
51		,		c.RootID					AS RootID
52		,		c.Stack + 1					AS Stack		
53		FROM	employees e
54		JOIN	__cte c
55			ON c.Id = e.ManagerID
56)
57
58SELECT	c.ID				AS ID
59,		c.Salary			AS Salary
60,		c.RootID			AS RootID
61-- ,		c.RootSalary		AS RootSalary
62,		c.RootSalaryDiff	AS RootSalaryDiff
63-- ,		c.Stack				AS Stack
64from __cte c
65where	c.RootSalaryDiff < 0
66order by id, RootID