员工经理层次结构 [英] Employee Manager Hierarchy
本文介绍了员工经理层次结构的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有一张表,其架构和样本数据如下:
EmpCode ManagerCode
========= =============
1 1
2 1
3 2
4 2
5 3
6 2
7 3
8 4
表的名称是EMP
现在我的任务是创建一个查询,它将根据特定的EmpCode提供管理器的层次结构。例如,对于EmpCode 7,输出将是
EmpCode
=======
1
2
3
我尝试过自我加入,但它只提供直接经理而不是总体层次结构。如果提供任何线索来实现这一结果将会有很大的帮助
解决方案
这个怎么样
从EMP中选择不同的managerid,其中managerid< =(从EMP中选择managerid,其中empid = 7)
也许下面的文章可以帮到你:
http://www.sqlteam.com/articl e / more-trees-hierarchyies-in-sql [ ^ ]
干杯
创建 procedure testemp( @ empid int )
as
开始
声明 @ temp int ;
创建 表 #temp(mgrid int < /跨度>,);
选择 @ temp = mgrid 来自 empmgr 其中 empid = @ empid;
插入 进入 #temp(mgrid)值( @ temp );
while ( @ temp != @ empid )
开始
选择 @ temp = mgrid, @ empid = empid 来自 empmgr 其中 empid = @温度;
插入 进入 #temp(mgrid)值( @ temp );
结束
选择 * 来自 #temp;
end
CREATE TABLE [dbo]。[empmgr](
[empid] [ int ] NULL ,
[mgrid] [ int ] NULL
) ON [ PRIMARY < /跨度>
Hi All,
I've a table whose schema and sample data are as follows:
EmpCode ManagerCode ========= ============= 1 1 2 1 3 2 4 2 5 3 6 2 7 3 8 4
The name of the table is EMP
Now my task is to create a query which will give a hierarchy of manager against a particular EmpCode. Say for example for EmpCode 7 the output will be
EmpCode ======= 1 2 3
I've tried with self join but its giving only the immediate manager not the total hierarchy. It will be of great help if provided with any clue to achieve this result
解决方案
What about THIS
select distinct managerid from EMP where managerid <= (select managerid from EMP where empid=7)
You could try building Recursive CTE[^] for data hirarchy.
See my newly written TIP[^] only for you.
Maybe the following article will help you :
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql[^]
Cheers
Hi,
create procedure testemp(@empid int) as begin declare @temp int; create table #temp (mgrid int,); select @temp=mgrid from empmgr where empid =@empid; insert into #temp (mgrid) values (@temp); while (@temp != @empid) begin select @temp=mgrid , @empid =empid from empmgr where empid =@temp; insert into #temp (mgrid) values (@temp); end select * from #temp; end
CREATE TABLE [dbo].[empmgr]( [empid] [int] NULL, [mgrid] [int] NULL ) ON [PRIMARY]
这篇关于员工经理层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文