SQL Server 2008中的分层树的SQL查询 [英] sql query for hierarchical tree in sql server 2008

问题描述

Hello All

我有一个结构表



Employee_Id int

EmpName Varchar（500）

SeniorId in



数据类似这样





Employee_Id EmpName SeniorId

1 A 0

2 B 1

3 C 2

4 D 3

5 E 0

6 F 5

7 G 6



我希望所有员工在任何员工之下以层级方式工作

Ex。在A - >下B，C，D

在B下 - > C，D

在E - >下; F，G



请帮我在Sql Server中编写Sql查询。



谢谢

Hello All
I Have a Table With Structure

Employee_Id int
EmpName Varchar(500)
SeniorId in

The Data Like This


Employee_Id EmpName SeniorId
1 A 0
2 B 1
3 C 2
4 D 3
5 E 0
6 F 5
7 G 6

I want all the Employees Under Any Any Employee in a hierarchical manner
For Ex. Under A -> B,C,D
Under B -> C,D
Under E -> F,G

Please Help me in Writing Sql Query in Sql Server.

Thanks

推荐答案

任何方式冷静：

你的解决方案：



select t2.Employee_Id as员工，t1.name作为来自员工t1的Senior_Name离开加入员工t2 on t1.Employee_Id = t2.Senior



结果将是



Employee_id Senoir

1 B



根据您的要求。

any ways chill:
your solution :

select t2.Employee_Id as Employee, t1.name as Senior_Name from Employees t1 left join Employees t2 on t1.Employee_Id=t2.Senior

result will be

Employee_id Senoir
1 B

as per your requirement.

感谢大家帮助我。以下是由高级管理人员直接或间接连接员工的代码。

Thanks To All For Helping Me. Here is a Code To Get Employee Connected Directly or Indirectly by a Senior.

with  CTE as ( select  Employee_Id     
        from    Employee
        where   SeniorId  = @Employee_Id
        union all
        select  child.Employee_Id       
       
        from    Employee child
        join    CTE parent
        on      child.SeniorId = parent.Employee_Id
        )
Select * from Emplyee Where Employee_Id in (select  * from    CTE)

这篇关于SQL Server 2008中的分层树的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！