OA项目中关于数据表递归查询

例如知道某员工顶级部门id（企业id）需要查询所有员工

正常Oracle递归

SELECT DISTINCT U.*,UD.ORDER_NUM 
FROM 
TBL_BASE_USER U ,TBL_BASE_USER_DEPT UD,(select*from tbl_base_dept t start with t.dept_id='17052413401200000190' connect by t.dept_parent_id=prior t.dept_id ) A
where          
U.USER_ID = UD.USER_ID 
AND U.USER_TYPE != '1000' 
AND U.USER_TYPE IS NOT NULL  
AND UD.DEPT_ID=A.DEPT_ID

ORDER BY ud.order_num

为了支持Mysql数据库，部门表中有id_path列，存放所有上级部门id，所以我们只需查找所有id_path中有当前顶级部门id的部门id

SELECT DISTINCT U.*,UD.ORDER_NUM 
FROM TBL_BASE_USER U ,TBL_BASE_USER_DEPT UD
where          
U.USER_ID = UD.USER_ID 
AND U.USER_TYPE != '1000' 
AND U.USER_TYPE IS NOT NULL  
AND UD.DEPT_ID in(select dept_id from tbl_base_dept where id_path like CONCAT(CONCAT('%,','17052413401200000190'),',%')) 
ORDER BY ud.order_num