自己如何网站建设,wordpress 功能介绍,做网站自动赚钱吗,国家企业信息认证系统介绍
说到递归查询#xff0c;大家可以想到的技术实现方式主要如下几种#xff1a; 1、各种主流应用开发语言本身通过算法实现 2、各种数据库引擎自身提供的算法实现
本文提到主要是针对第二种和第一种的结合
主要技术栈
1、ORM#xff1a;Mybatis 2、DB#xff1a;MyS…介绍
说到递归查询大家可以想到的技术实现方式主要如下几种 1、各种主流应用开发语言本身通过算法实现 2、各种数据库引擎自身提供的算法实现
本文提到主要是针对第二种和第一种的结合
主要技术栈
1、ORMMybatis 2、DBMySQL 3、开发语言Java
代码实现示例
方式一、Mybatis Mapper类实现代码示例如下 // dept_employee 员工和部门的关系表
// dept 部门信息表
// 根据员工编号查询其直属部门的所有下级部门节点
Select({script,with recursive child_dept as\n (\n select id, code,name,name as dept_structure\n from dept\n where id in (\n select DISTINCT dept_id from dept_employee\n where employee_id #{employeeId} when testtype!null,and type#{type}\n /when)\n union\n select a.id,a.code ,a.name, concat(b.dept_structure, , a.name) as dept_structure\n from child_dept as b\n join dept as a on b.codea.parent_code\n )\n select DISTINCT id from child_dept;\t,/script})ListLong queryAllChildDeptByEmployeeId(Param(employeeId) Long employeeId,Param(type) String type);
方式二、Mybatis存储过程示例如下 // Mybatis Mapper类调用存储过程Select(call queryAllChildDeptByEmployeeId(#{employeeId},#{type}))Options(statementType StatementType.CALLABLE)ListLong queryAllChildDeptByEmployeeId(Param(employeeId) Long employeeId, String type);
//存储过程示例
CREATE DEFINERtest% PROCEDURE queryAllChildDeptByEmployeeId(IN employeeId bigint,IN type VARCHAR(10))
BEGIN
if type is null or type THENwith recursive child_dept as(select id, code,name,name as dept_structurefrom deptwhere id in (select DISTINCT dept_id from dept_employeewhere employee_id employeeId )unionselect a.id,a.code ,a.name, concat(b.dept_structure, , a.name) as dept_structurefrom child_dept as bjoin dept as a on b.codea.parent_code)select DISTINCT id from child_dept;
ELSEwith recursive child_dept as(select id, code,name,name as dept_structurefrom deptwhere id in (select DISTINCT dept_id from dept_employeewhere employee_id employeeId and typetype)unionselect a.id,a.code ,a.name, concat(b.dept_structure, , a.name) as dept_structurefrom child_dept as bjoin dept as a on b.codea.parent_code)select DISTINCT id from child_dept;
END IF;
END踩坑介绍
以上实现方式涉及到了MySQL的with as 语法此实现方式必须给予MySQL 8.0.x版本5.x.x版本不支持
解决方案 1、升级数据库版本为8.x版本 2、采用5.x版本语法FIND_IN_SET,如下代码示例
SELECT au.id, au.name, au.pidFROM (SELECT * FROM dept WHERE pid IS NOT NULL) au,(SELECT pid : 1002,1005) pdWHERE FIND_IN_SET(pid, pid) 0 and pid : concat(pid, ,, id)
UNION
SELECT id, name, pidFROM deptWHERE FIND_IN_SET(id, pid) 0ORDER BY id; 3、采用应用开发语言实现如Java代码实现
