如何使用SQL查询获取分层数据 [英] How to get Hierarchical Data with SQL query

问题描述

大家好

我想要满足以下要求的sqlquery

我有2个表"EMPLOYEE"和"EMP_REPORTING"，每个雇员都有报告人.报告人将是另一个雇员.

如果阿姆鲁莎向阿妮莎(Anisha)汇报
阿尼沙(Anisha)向杰尼沙(Janisha)汇报 Janisha向Arjun汇报

登录amrutha时，她需要访问自己的详细信息
当anisha登录时，她可以访问amrutha和anishas
当Janisha登录时，她可以访问amrutha，anishas和janisha的
当arjun登录时，她可以访问amrutha，anishas和janisha和arjuns

员工表字段是Emp_ID，Emp_Name
报告表包含字段rep_id，emp_id.rep_emp_id

我想让所有举报要登录的员工以及举报给
的员工感到欣慰 在他之下的员工

在此先感谢
我认为以下SQL查询可用于检索向登录员工报告或向直接员工报告或直接向员工报告的所有员工间接向登录员工报告.

 - 用于保存顶级搜索员工ID的本地变量
 DECLARE   @ SearchId   nvarchar ( 50 )
 SET   @ SearchId  = '  4'

- 将Employee Reporting表转换为Flat 
- 使用递归的报表
; 与 FlatReporting  AS (
选择 Emp_Id，Rep_Emp_Id， 1   as 级别
 FROM  EmpReporting
位置 Rep_Emp_Id =  @ SearchId 
联盟(Union) 所有

选择 E1.Emp_Id，E1.Rep_Emp_Id，级别+  1 
 FROM  EmpReporting E1
 INNER   JOIN  FlatReporting E2
打开 E1.Rep_Emp_Id = E2.Emp_Id
)
选择 Emp_Id，
  - 从员工表中获取Emp_Name 
  - 前1个用于确保仅返回一个值
        Emp_Name =(
            选择  TOP   1  Emp_Name
     FROM 员工
    位置 Employee.Emp_Id = FlatReporting.Emp_Id)，
等级
 FROM  FlatReporting
联盟(Union) 所有

- 与雇员"表中的顶层雇员合并
选择 Emp_Id，Emp_Name， 0  作为级别
 FROM 员工
位置 Emp_Id =  @ SearchId 
- 按报告级别排序
订单  BY 级别

- 输出
-  Emp_Id Emp_Name级别
-  4 Arjun 0 
-  3 Janisha 1 
-  2阿妮莎2 
-  1 Amrutha 3 

- 输入内容如下
- 员工表
-  Emp_Id Emp_Name 
-  1个Amrutha 
-  2阿尼莎(Anisha)
-  3 Janisha 
-  4个Arjun 

-  EmpReporting表
-  Rep_Id Emp_Id Rep_Emp_Id 
-  R1 1 2 
-  R2 2 3 
-  R3 3 4  

朋友，

我认为如果您在Emp_reporting中再添加一列emp_type，则根据emp_type的类型，您可以从表中检索报告.

在前端登录时，检查/让他选择自己的雇员类型?，具体取决于检索报告的方式.

Hi All

I want sqlquery for following requirement

I have 2 tables ''EMPLOYEE'' and "EMP_REPORTING'' each employee have reporting person. Reporting person will be another employee.

If Amrutha Reporting to Anisha
Anisha reporing to Janisha
Janisha reporting to Arjun

when amrutha login she needs to access her own details
when anisha login she can access amrutha''s and anishas
when Janisha login she can access amrutha''s, anishas and janisha''s
when arjun login she can access amrutha''s, anishas and janisha''s and arjuns

Employee table fields are Emp_ID,Emp_Name
Reporting table consist of fields rep_id,emp_id.rep_emp_id

i want to get empids of all employees who reporting to login employee and also employees reporting to
employee under him

Thanks in Advance
Amrutha

解决方案

I think the following SQL Query can be used to retrieve all the employees who are either reporting to the login employee or reporting to an employee who is either directly or indirectly reporting to the login employee.

--Local variable to hold the Top level search Employee Id
DECLARE @SearchId nvarchar(50)
SET @SearchId = '4'

--Convert the Employee Reporting table into a Flat 
--Reporting table using Recursion
;WITH FlatReporting AS (
	SELECT Emp_Id, Rep_Emp_Id, 1 as Level
	FROM EmpReporting
	WHERE Rep_Emp_Id = @SearchId
	UNION ALL
	
	SELECT E1.Emp_Id, E1.Rep_Emp_Id, Level + 1
	FROM EmpReporting E1
	INNER JOIN FlatReporting E2
	ON E1.Rep_Emp_Id = E2.Emp_Id
)
SELECT Emp_Id, 
  --Get Emp_Name from Employee Table
  --Top 1 is used to ensure that only one value is returned
        Emp_Name = (
            SELECT TOP 1 Emp_Name 
    	    FROM Employee 
    	    WHERE Employee.Emp_Id = FlatReporting.Emp_Id), 
	Level
FROM FlatReporting
UNION ALL

--Combine with the top level employee from Employee table
SELECT Emp_Id, Emp_Name, 0 as Level
FROM Employee
WHERE Emp_Id = @SearchId
--Order by the reporting level
ORDER BY Level

--Output
--Emp_Id Emp_Name  Level
--4	Arjun	0
--3	Janisha	1
--2	Anisha	2
--1	Amrutha	3

--Where Input is as follows
--Employee Table
--Emp_Id	Emp_Name
--1	Amrutha
--2	Anisha
--3	Janisha
--4	Arjun

--EmpReporting table
--Rep_Id	Emp_Id	Rep_Emp_Id
--R1	1	2
--R2	2	3
--R3	3	4

Hi friend,

I think if u add one more column emp_type to Emp_reporting, then depending on the type of emp_type u can retrive the reports from the table.

In front end while log in check/make him to select what type of employee he/she is?, depending on that retrieve the reports.

这篇关于如何使用SQL查询获取分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！