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
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
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
1min目标场景问卷
立即参与
