求助高朋:附件数据是一个8级架构的递归表,如何写一个mysql的查询语句,得到一个扁平化的二维表(图

求助高朋:附件数据是一个8级架构的递归表,如何写一个mysql的查询语句,得到一个扁平化的二维表(图1)?特别说明:部门的编码没有规则,只是保证了唯一性。2019-09-12_17-56-48.png


t_crm_department.xlsx


alu 发布于 2019-9-12 18:02 (编辑于 2019-9-16 09:03)
1min目标场景问卷 立即参与
回答问题
悬赏:4 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共2回答
最佳回答
5
nide870716Lv3见习互助
发布于2019-10-15 14:27

拆成二维表不方便用递归,下面脚本供参考

select a.departmentname as "一级部门"
,b.departmentname as "二级部门"
,c.departmentname as "三级部门"
,d.departmentname as "四级部门"
,e.departmentname as "五级部门"
,f.departmentname as "六级部门"
,g.departmentname as "七级部门"
,h.departmentname as "八级部门"
from t_crm_department a
left join t_crm_department b on b.departmenttype =2 and a.departmentid =b.parentdeptid
left join t_crm_department c on c.departmenttype =3 and b.departmentid =c.parentdeptid
left join t_crm_department d on d.departmenttype =4 and c.departmentid =d.parentdeptid
left join t_crm_department e on e.departmenttype =5 and d.departmentid =e.parentdeptid
left join t_crm_department f on f.departmenttype =6 and e.departmentid =f.parentdeptid
left join t_crm_department g on g.departmenttype =7 and f.departmentid =g.parentdeptid
left join t_crm_department h on h.departmenttype =8 and g.departmentid =h.parentdeptid
where a.departmenttype =1
order by a.departmentid,b.departmentid,c.departmentid,d.departmentid,e.departmentid,f.departmentid,g.departmentid,h.departmentid

fr6.png

  • alu alu(提问者) 非常感谢!基本快达成目的了。但是怎样才能增加“所属机构id”这一列呢?
    2019-10-17 09:09 
  • nide870716 nide870716 回复 alu(提问者) 你给的附件里没有\"所属机构id\"信息,如果这一列有编码的规则,可以说明下,看下能否算出来
    2019-10-17 11:13 
  • alu alu(提问者) 回复 nide870716 我说的“所属机构id”就是附件中的A列——departmentid。转换后的每一行都会有一个departmentid
    2019-10-17 15:41 
  • nide870716 nide870716 回复 alu(提问者) 看看这个脚本执行结果能否满足你的要求 select a.departmentid as \"一级部门ID\",a.departmentname as \"一级部门\" ,b.departmentid as \"二级部门ID\",b.departmentname as \"二级部门\" ,c.departmentid as \"三级部门ID\",c.departmentname as \"三级部门\" ,d.departmentid as \"四级部门ID\",d.departmentname as \"四级部门\" ,e.departmentid as \"五级部门ID\",e.departmentname as \"五级部门\" ,f.departmentid as \"六级部门ID\",f.departmentname as \"六级部门\" ,g.departmentid as \"七级部门ID\",g.departmentname as \"七级部门\" ,h.departmentid as \"八级部门ID\",h.departmentname as \"八级部门\" from t_crm_department a left join t_crm_department b on b.departmenttype =2 and a.departmentid =b.parentdeptid left join t_crm_department c on c.departmenttype =3 and b.departmentid =c.parentdeptid left join t_crm_department d on d.departmenttype =4 and c.departmentid =d.parentdeptid left join t_crm_department e on e.departmenttype =5 and d.departmentid =e.parentdeptid left join t_crm_department f on f.departmenttype =6 and e.departmentid =f.parentdeptid left join t_crm_department g on g.departmenttype =7 and f.departmentid =g.parentdeptid left join t_crm_department h on h.departmenttype =8 and g.departmentid =h.parentdeptid where a.departmenttype =1 order by a.departmentid,b.departmentid,c.departmentid,d.departmentid,e.departmentid,f.departmentid,g.departmentid,h.departmentid
    2019-10-18 11:52 
  • alu alu(提问者) 回复 nide870716 首先非常感谢您的回复! 上帖还不是我的需求,我所说的departmentid是每一行最后一级部门的id
    2019-10-18 17:47 
最佳回答
0
豆豆小可爱Lv4中级互助
发布于2019-9-16 09:39(编辑于 2019-9-16 09:41)

我只会这么写

image.png

SELECT A.departmentid AS 部门ID,A.departmentname AS 一级部门,B.departmentname AS 二级部门,C.departmentname AS 三级部门,D.departmentname AS 四级部门
,E.departmentname AS 五级部门,F.departmentname AS 六级部门,G.departmentname AS 七级部门,H.departmentname AS 八级部门
FROM (SELECT * FROM t_crm_department WHERE departmenttype='8') A
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='7') B ON A.parentdeptid=B.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='6') C ON B.parentdeptid=C.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='5') D ON C.parentdeptid=D.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='4') E ON D.parentdeptid=E.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='3') F ON E.parentdeptid=F.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='2') G ON F.parentdeptid=G.departmentid
LEFT JOIN (SELECT * FROM t_crm_department WHERE departmenttype='1') H ON G.parentdeptid=H.departmentid
  • alu alu(提问者) 多谢回答! 可惜不是我要的结果,因为丢了很多部门
    2019-09-17 13:11 
  • 3关注人数
  • 481浏览人数
  • 最后回答于:2019-10-15 14:27
    请选择关闭问题的原因
    确定 取消
    返回顶部