这一篇融合了三个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