对于员工表中的每个员工，在层次结构中找到直接和间接老板的数量 [英] For each employee in employee table, find number of direct and indirect bosses in hierarchy

问题描述

我给了一个雇员表，看起来像这样:

查询以创建示例输入.

  CREATE TABLE员工(empId INTEGER，empName VARCHAR(20)，mgrId INTEGER，薪水DECIMAL(12,2));插入员工价值观(1，'A'，2，100)，(2，'B'，4，150)，(3，'C'，4，165)，(4，'D'，7，200)，(5，'E'，6，210)，(6，'F'，7，250)，(7，'G'，7，300)，(8，'H'，6，170); 

链接到SQL Fiddle:

每位员工都有直接上司.同样，将所有在给定雇员之上的雇员称为其间接老板.在给定的样本中，A的直接老板是B，而C，D，E，F，G和H是A的间接老板.我必须找到两件事.

1. 对于每个雇员，找到该雇员之前所有雇员的人数(直接上司+间接上司的数量).

预期输出:

  + ------------- + ------- +|empName |总计|+ --------- + ------- +|A |7 ||B |3 ||C |3 ||D |1 ||E |3 ||F |1 ||G |0 ||H |3 |+ --------- + ------- + 

2. 对于每位员工，以这样的方式找到间接老板:间接老板的薪水至少是给定员工薪水的两倍，但在间接老板中最低.

预期输出:

  + --------- ++ ------ +|empName |mgr |+ --------- + ------ +|A |D ||B |G ||C |NULL ||D |NULL ||E |NULL ||F |NULL ||G |NULL ||H |NULL |+ --------- + ------ + 

说明:对于雇员A(薪水= 100)，具有至少两倍薪水的间接老板是D(薪水= 200)，F(薪水= 210)和G(薪水= 300).但是由于D，F和G的D工资是最低的，所以结果是D.

很容易找到员工下属的人数，但反之则非常棘手.任何帮助/提示将不胜感激.

解决方案

我希望您能找到以下答案，直到提出更理想的解决方案为止.

第一步是创建一个新视图，其中记录了员工的级别.在这种情况下，名为"G"的老板"具有最小的值1.而对于每个级别的员工，其等级正在增加，其中A的最高级别为4.该视图使用层次查询构造为http://sqlfiddle.com/#!9/cd4be8

This sample data results in this hierarchy.

Each employee has a direct boss. Also, all the employees who are senior to a given employee are referred to as their indirect bosses. In the given sample, the direct boss of A is B, and C, D, E, F, G, and H are A's indirect bosses. I am required to find two things.

1. For each employee, find the count of all the employees who are senior to that employee (number of direct boss + indirect bosses).

Expected output:

+---------+-------+
| empName | total |
+---------+-------+
| A       |     7 |
| B       |     3 |
| C       |     3 |
| D       |     1 |
| E       |     3 |
| F       |     1 |
| G       |     0 |
| H       |     3 |
+---------+-------+

2. For each employee, find the indirect boss in such a way that indirect boss's salary is at least the twice the salary of the given employee but minimum among the indirect bosses.

Expected output:

+---------+------+
| empName | mgr  |
+---------+------+
| A       | D    |
| B       | G    |
| C       | NULL |
| D       | NULL |
| E       | NULL |
| F       | NULL |
| G       | NULL |
| H       | NULL |
+---------+------+

Explanation: for employee A (salary = 100), the indirect bosses with at least double salary are D (salary = 200), F (salary = 210) AND G (salary = 300). But since D's salary is minimum for D, F and G, the result is D.

It is easy to find the count of an employee's subordinates, but the other way round is something very tricky. Any help/hints would be highly appreciated.

解决方案

I hope you will find the following answers helpful until a more optimal solution comes up.

The first step creates a new view where the level of the employee is recorded. The "boss", named G in this occasion, has the smallest value 1. While the rank is increasing for each level of employee with the highest been 4 for A. The view is constructed with a hierarchical query as here.

After creating the view, the first question is answered by summing all the employees in the higher rank. This is done for every rank and the join brings the information to the final view.

The second question is answered on a more brute force way. A self-join to produce all the viable combinations of employee and bosses is performed. After that the lines with the minimum boss salary are produced.

--Creating a view with the level of the diagram
CREATE VIEW MyCTEView 
AS 
    WITH my_anchor AS (
        SELECT boss.[empId], boss.[empName], boss.[mgrId], 1 AS EmpLevel
        FROM [MySchema].[dbo].[employee] AS boss
        WHERE boss.[mgrId]=boss.[empId]

        UNION ALL

        SELECT Emp.[empId], Emp.[empName], Emp.[mgrId], EL.EmpLevel+1
        FROM [MySchema].[dbo].[employee] AS Emp
        INNER JOIN my_anchor as EL
        ON Emp.[mgrId] = EL.[empId]
        WHERE Emp.[mgrId]<>Emp.[empId]
        )
    SELECT * FROM my_anchor;


--Answer to the first question
SELECT A.[empName]
      ,temp.direct_and_indirect-1 AS your_total
FROM [MySchema].[dbo].[MyCTEView] AS A
LEFT JOIN (
SELECT [EmpLevel],SUM(direct_subortinates) OVER(ORDER BY [EmpLevel]) AS direct_and_indirect
FROM (SELECT COUNT([mgrId]) AS direct_subortinates,[EmpLevel]
            FROM [MySchema].[dbo].[MyCTEView] GROUP BY [EmpLevel])T) AS Temp
ON Temp.[EmpLevel]=A.[EmpLevel]
ORDER BY A.[empName]


--Answer to the second question. Creating a CTE with all the viable combinations of employee and manager based on criteria.
--Displaying the information for the minimum
WITH cte AS (
SELECT A.[empId] as emId
      ,A.[empName] as emName
      ,A.[salary] as emsalary
      ,A.[EmpLevel] as emLevel
      ,B.[empId] as bossId
      ,B.[empName] as bossName
      ,B.[salary] as bosssalary
      ,B.[EmpLevel] as bossLevel
  FROM [MySchema].[dbo].[MyCTEView] AS A
  INNER JOIN 
  [MySchema].[dbo].[MyCTEView] AS B
  ON A.empId<>B.empId AND A.[EmpLevel]>B.[EmpLevel] AND B.[salary]>=2*A.[salary]
  )
SELECT tb1.emName, tb1.bossName 
FROM cte AS tb1
  INNER JOIN
  (
    SELECT emName, MIN(bosssalary) MinSalary
    FROM cte
    GROUP BY emName
  )tb2
  ON  tb1.emName=tb2.emName
  WHERE tb1.bosssalary=tb2.MinSalary

这篇关于对于员工表中的每个员工，在层次结构中找到直接和间接老板的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！