这个怎么样

从EMP中选择不同的managerid，其中managerid< =（从EMP中选择managerid，其中empid = 7）

您可以尝试构建递归CTE [ ^ ]用于数据hirarchy。



请参阅我新写的提示 [ ^ ]仅供您使用。

也许下面的文章可以帮到你：

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]