mysql - 对于员工表中的每个员工，查找层次结构中直接和间接老板的数量

Question

我得到一个看起来像这样的员工表:

创建示例输入的查询。

CREATE TABLE employee(
    empId INTEGER,
    empName VARCHAR(20),
    mgrId INTEGER,
    salary DECIMAL(12,2)
);

INSERT INTO employee VALUES
(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 链接:http://sqlfiddle.com/#!9/cd4be8

此示例数据导致此层次结构。

每个员工都有一个直属上司。此外，所有比给定员工年长的员工都被称为他们的间接老板。在给定的样本中，A的直属上司是B，C、D、E、F、G、H是A的间接上司。我需要找到两件事。

1. 对于每位员工，找出比该员工年长的所有员工的数量(直接老板的数量 + 间接老板的数量)。

预期输出:

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

1. 对于每个员工，以间接老板的薪水至少是给定员工薪水的两倍但在间接老板中最小的方式找到间接老板。

预期输出:

+---------+------+
| 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 的薪水是 D、F 和 G 的最低值，所以结果是 D。

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

Answer 1

在出现更优化的解决方案之前，我希望您会发现以下答案对您有所帮助。

第一步创建一个新 View ，其中记录了员工的级别。在这种情况下名为 G 的“老板”具有最小值 1。随着员工级别的增加，A 的级别最高为 4。 View 是使用分层查询构建的 here。 .

创建 View 后，第一个问题是通过对所有较高级别的员工求和来回答的。这是为每个等级完成的，连接将信息带到最终 View 。

第二个问题以更暴力的方式回答。执行自连接以产生员工和老板的所有可行组合。之后生产老板工资最低的线路。

--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