MySQL面试题_员工关系层级查询「终于解决」

欢迎大家来到IT世界,在知识的湖畔探索吧!

这一篇融合了三个MySQL面试的问题，也是工作中很常见的的需求，虽然还是关系型数据表，但是确实呈现的树结果的逻辑关系，最常见的就是如下图所示的员工层级的关系表。从组织架构来说，扁平化的公司一般都有3-5层，如果层级较多的公司可能都要去到10+层了↓

但是员工表都是按标准的关系型数据库存的，如下图，主要有员工ID、员工姓名和对应的上级领导，然后应该还有其他一些员工属性信息。比如刘备没有上级，对应就是空白；赵云对应上级是诸葛亮，下级有魏延、邓芝和马岱。邓芝不能直接到刘备，需要层层关系邓芝→赵云→诸葛亮→刘备，在这里需要经历三个层级才能到刘备。

我们这一篇要解决三个问题，第一个问题是找出公司的老板，管理层和基层员工。第二个问题是需要找到每一个员工对应上级的姓名。第三个问题要复杂一点，就是找到每个员工的层级关系图，（邓芝→赵云→诸葛亮→刘备）这种。下面开始↓

首先是第一个问题，表结构已经上面给出了。需要找到三个层级（老板、管理层、基层）。我们这里对老板的定义是没有上级；对中层的定义是有下级就算；剩下的就是基层员工了。

思路还是很简单，只需要做一下判断就行了。老板就判断上级自带时候有信息，没有信息就是老板；中层就判断一下，如果自己的员工ID出现在了上级的ID里面，说明自己属于上级，只要不是老板就是中层了；然后剩下来的就是基层了。SQL语句如下↓

SELECT
  emp_id, `emp_name`,
  CASE 
    WHEN manager is null THEN "老板" 
    WHEN manager is not null AND emp_id IN (SELECT DISTINCT manager FROM `employee`) THEN "管理层"
    ELSE "基层员工"
  END as type
FROM
  `employee`;

欢迎大家来到IT世界,在知识的湖畔探索吧!

简单解释一下，主要是用了CASE WHEN语句做了一下三层的条件判断，主要是判断管理层这里用到了一个子查询，就是用来判断自己的ID是否在上级的ID里面，如果是并且不是老板就属于管理层了，最后的结果如下↓

然后是第二个问题，找到每一个员工对应上级的姓名，这也是实际中最常用的，或者是找到上级部门，都是一个意思。这个问题也很简单，思路就是用员工ID去匹配一下上级ID，做一个连接查询就行了，SQL语句和结果如下↓

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT
  e1.emp_name AS 员工姓名,
  e2.emp_name AS 上级
FROM
  employee AS e1,employee AS e2
WHERE
  e1.manager = e2.emp_id;

第三个问题是今天的难点，要找个员工对应所有的层级。（邓芝→赵云→诸葛亮→刘备）这种，当然每个人的层级是不一样了。这里就需要用到循环也就是MySQL里面的递归查询了。思路是从最上层开始，就是刘备，创建一个基础信息查询；然后通过循环找到刘备的直接下属（诸葛亮、关羽、张飞），这是第一轮循环；然后继续用这三个人的ID的循环找到这三个人分别对应的下属。依次循环下去，直到没有了下属就结束了。SQL语句和结果如下↓

WITH RECURSIVE cte AS(
  SELECT
    emp_id, emp_name, emp_name AS path
  FROM
    `employee`
  WHERE
    emp_id = 1
  UNION ALL
  SELECT 
    e.emp_id, e.emp_name, CONCAT(e.emp_name,"←",m.path)
  FROM 
    `employee` AS e
    JOIN cte AS m ON (m.emp_id = e.manager)
)
SELECT * FROM cte;

WITH RECURSIVE这种递归用法在MySQL8.0后才有，之前也介绍过几篇使用递归来解决其他问题的文章，可以看看加深理解，这个在处理一些复制的结构时还是很有用的。

最后我们把这三个条件结合到一起，就可以查询所有的信息了，SQL语句和结果如下↓

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT 
  t1.*,t2.上级,t3.path
FROM
  (SELECT
    emp_id, `emp_name`,
    CASE 
      WHEN manager is null THEN "老板" 
      WHEN manager is not null AND emp_id IN (SELECT DISTINCT manager FROM `employee`) THEN "管理层"
      ELSE "基层员工"
    END as type
  FROM
    `employee`) t1
LEFT JOIN
  (SELECT
    e1.emp_id,
    e1.emp_name AS 员工姓名,
    e2.emp_name AS 上级
  FROM
    employee AS e1,employee AS e2
  WHERE
    e1.manager = e2.emp_id) t2
ON t1.emp_id = t2.emp_id
LEFT JOIN
(WITH RECURSIVE cte AS(
  SELECT
    emp_id, emp_name, emp_name AS path
  FROM
    `employee`
  WHERE
    emp_id = 1
  UNION ALL
  SELECT 
    e.emp_id, e.emp_name, CONCAT(e.emp_name,"→",m.path)
  FROM 
    `employee` AS e
    JOIN cte AS m ON (m.emp_id = e.manager)
)
SELECT * FROM cte) t3
ON t1.emp_id = t3.emp_id

今天就到这里，后面还会继续分享一些有趣实用的MySQL问题。

End