时间戳

首页 > 解决方案 > 对于员工表中的每个员工，查找层次结构中直接和间接老板的数量

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

问题描述

我得到一个员工表，看起来像这样：

在此处输入图像描述

用于创建示例输入的查询。

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 小提琴：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。

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

标签： mysqlsqlhierarchy

解决方案

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

第一步创建一个记录员工级别的新视图。在这种情况下名为 G 的“老板”具有最小值 1。而员工的每个级别的级别都在增加，A 的最高级别为 4。视图是使用分层查询构造的，如下所示。

创建视图后，第一个问题通过将所有较高级别的员工相加来回答。这是针对每个等级完成的，并且连接将信息带到最终视图。

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

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

推荐阅读