SQL查询 - :首先列出一个管理员,然后是所有子管理员,然后是第二个管理员,然后是他的下属,依此类推 [英] SQL Query - : list one of the manager first, then all his sub-ordinates, then second manager and then his subordinates and so on
本文介绍了SQL查询 - :首先列出一个管理员,然后是所有子管理员,然后是第二个管理员,然后是他的下属,依此类推的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的Orignal表是
Id姓名薪资经理_id
1 A 100 NULL
2 B 300 1
3 C 200 1
4 D 100 5
5 E 50 NULL
6。 。 。
我希望输出为
Employee_name Manager_name
A NULL
BA
CA
E NULL
DE
我试过这个查询
选择 < span class =code-keyword> top 5 e.name as Employee,m .name as 经理
来自 EMPLOYEE e
左 加入员工m e.Manager_id = m.ID
但它给我的输出为
员工经理
A NULL
BA
CA
DE
E NULL
请帮我完成这个...
解决方案
尝试以下方法解决您的问题。可以解决你的问题。
DECLARE @table TABLE(id INT NOT NOT PRIMARY KEY,name NVARCHAR( 4000)NOT NULL,路径HIERARCHYID)
INSERT
INTO @table
VALUES
(1,'Root','/'),
( 2,'A','/ 1 /'),
(3,'E','/ 2 /'),
(4,'B','/ 1/1 /') ,
(5,'C','/ 1/2 /'),
(7,'D','/ 2/1 /'),
(8,'F ','/ 2/2 /')
; WITH q AS
(
SELECT *,HIERARCHYID :: Parse('/')AS newpath
FROM @table
WHERE path = HIERARCHYID :: GetRoot()
UNION ALL
SELECT t。*,HIERARCHYID :: Parse(q.newpath.ToString()+ CAST(ROW_NUMBER()OVER( ORDER BY t.name)AS NVARCHAR(MAX))+'/')
FROM q
JOIN @table t
ON t.path.IsDescendantOf(q.path)= 1
AND t.path.GetLevel()= q.path.GetLevel()+ 1
)
SELECT q.name
FROM q
其中q.name!='Root'
ORDER BY
newpath
My Orignal Table is
Id Name Salary Manager_id 1 A 100 NULL 2 B 300 1 3 C 200 1 4 D 100 5 5 E 50 NULL 6 . . .
and i want the output as
Employee_name Manager_name A NULL B A C A E NULL D E
I have tried with this query
select top 5 e.name as Employee, m.name as Manager
from EMPLOYEE e
left join Employee m on e.Manager_id = m.ID
but it is giving me the output as
Employee Manager
A NULL
B A
C A
D E
E NULL
Please help me to complete this...
解决方案
Try below approach for your problem. Might fix your issue.
DECLARE @table TABLE (id INT NOT NULL PRIMARY KEY, name NVARCHAR(4000) NOT NULL, path HIERARCHYID) INSERT INTO @table VALUES (1, 'Root', '/'), (2, 'A', '/1/'), (3, 'E', '/2/'), (4, 'B', '/1/1/'), (5, 'C', '/1/2/'), (7, 'D', '/2/1/'), (8, 'F', '/2/2/') ;WITH q AS ( SELECT *, HIERARCHYID::Parse('/') AS newpath FROM @table WHERE path = HIERARCHYID::GetRoot() UNION ALL SELECT t.*, HIERARCHYID::Parse(q.newpath.ToString() + CAST(ROW_NUMBER() OVER (ORDER BY t.name) AS NVARCHAR(MAX)) + '/') FROM q JOIN @table t ON t.path.IsDescendantOf(q.path) = 1 AND t.path.GetLevel() = q.path.GetLevel() + 1 ) SELECT q.name FROM q where q.name != 'Root' ORDER BY newpath
这篇关于SQL查询 - :首先列出一个管理员,然后是所有子管理员,然后是第二个管理员,然后是他的下属,依此类推的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文