这一篇融合了三个MySQL面试的问题，也是工作中很常见的的需求，虽然还是关系型数据表，但是确实呈现的树结果的逻辑关系，最常见的就是如下图所示的员工层级的关系表。从组织架构来说，扁平化的公司一般都有3-5层，如果层级较多的公司可能都要去到10+层了↓
但是员工表都是按标准的关系型数据库存的，如下图，主要有员工ID、员工姓名和对应的上级领导，然后应该还有其他一些员工属性信息。比如刘备没有上级，对应就是空白；赵云对应上级是诸葛亮，下级有魏延、邓芝和马岱。邓芝不能直接到刘备，需要层层关系邓芝→赵云→诸葛亮→刘备，在这里需要经历三个层级才能到刘备。
我们这一篇要解决三个问题，第一个问题是找出公司的老板，管理层和基层员工。第二个问题是需要找到每一个员工对应上级的姓名。第三个问题要复杂一点，就是找到每个员工的层级关系图，（邓芝→赵云→诸葛亮→刘备）这种。下面开始↓
首先是第一个问题，表结构已经上面给出了。需要找到三个层级（老板、管理层、基层）。我们这里对老板的定义是没有上级；对中层的定义是有下级就算；剩下的就是基层员工了。
思路还是很简单，只需要做一下判断就行了。老板就判断上级自带时候有信息，没有信息就是老板；中层就判断一下，如果自己的员工ID出现在了上级的ID里面，说明自己属于上级，只要不是老板就是中层了；然后剩下来的就是基层了。SQL语句如下↓
SELECT
emp_id,`emp_name`,
CASE
WHENmanagerisnullTHEN"老板"
WHENmanagerisnotnullANDemp_idIN(SELECTDISTINCTmanagerFROM`employee`)THEN"管理层"
ELSE"基层员工"
ENDastype
FROM
`employee`;
简单解释一下，主要是用了CASEWHEN语句做了一下三层的条件判断，主要是判断管理层这里用到了一个子查询，就是用来判断自己的ID是否在上级的ID里面，如果是并且不是老板就属于管理层了，最后的结果如下↓
然后是第二个问题，找到每一个员工对应上级的姓名，这也是实际中最常用的，或者是找到上级部门，都是一个意思。这个问题也很简单，思路就是用员工ID去匹配一下上级ID，做一个连接查询就行了，SQL语句和结果如下↓
SELECT
e1.emp_nameAS员工姓名,
e2.emp_nameAS上级
FROM
employeeASe1,employeeASe2
WHERE
e1.manager=e2.emp_id;
第三个问题是今天的难点，要找个员工对应所有的层级。（邓芝→赵云→诸葛亮→刘备）这种，当然每个人的层级是不一样了。这里就需要用到循环也就是MySQL里面的递归查询了。思路是从最上层开始，就是刘备，创建一个基础信息查询；然后通过循环找到刘备的直接下属（诸葛亮、关羽、张飞），这是第一轮循环；然后继续用这三个人的ID的循环找到这三个人分别对应的下属。依次循环下去，直到没有了下属就结束了。SQL语句和结果如下↓
WITHRECURSIVEcteAS(
SELECT
emp_id,emp_name,emp_nameASpath
FROM
`employee`
WHERE
emp_id=1
UNIONALL
SELECT
e.emp_id,e.emp_name,CONCAT(e.emp_name,"←",m.path)
FROM
`employee`ASe
JOINcteASmON(m.emp_id=e.manager)
)
SELECT*FROMcte;
WITHRECURSIVE这种递归用法在MySQL8.0后才有，之前也介绍过几篇使用递归来解决其他问题的文章，可以看看加深理解，这个在处理一些复制的结构时还是很有用的。
最后我们把这三个条件结合到一起，就可以查询所有的信息了，SQL语句和结果如下↓
SELECT
t1.*,t2.上级,t3.path
FROM
(SELECT
emp_id,`emp_name`,
CASE
WHENmanagerisnullTHEN"老板"
WHENmanagerisnotnullANDemp_idIN(SELECTDISTINCTmanagerFROM`employee`)THEN"管理层"
ELSE"基层员工"
ENDastype
FROM
`employee`)t1
LEFTJOIN
(SELECT
e1.emp_id,
e1.emp_nameAS员工姓名,
e2.emp_nameAS上级
FROM
employeeASe1,employeeASe2
WHERE
e1.manager=e2.emp_id)t2
ONt1.emp_id=t2.emp_id
LEFTJOIN
(WITHRECURSIVEcteAS(
SELECT
emp_id,emp_name,emp_nameASpath
FROM
`employee`
WHERE
emp_id=1
UNIONALL
SELECT
e.emp_id,e.emp_name,CONCAT(e.emp_name,"→",m.path)
FROM
`employee`ASe
JOINcteASmON(m.emp_id=e.manager)
)
SELECT*FROMcte)t3
ONt1.emp_id=t3.emp_id
今天就到这里，后面还会继续分享一些有趣实用的MySQL问题。
End