MyBatis 查询当前部门以及其部门下所有子级部门

最新推荐文章于 2024-06-19 16:13:31 发布

seesaw7

最新推荐文章于 2024-06-19 16:13:31 发布

阅读量1k

点赞数

文章标签： mybatis 数据库 mysql

本文链接：https://blog.csdn.net/Young_yaru/article/details/129203139

版权

在mysql中执行成功的查询语句（多表联合关系查询）

-- 定义变量并赋值为下面sql使用，【'hulianwang'】为代码传过来的值，改为你的即可。
SET @pnos := 'hulianwang';
SELECT
	id,
	personnelName,
	departmentCode,
	departmentName 
FROM
	(
SELECT
	pm_personnel.id,
	pm_personnel.personnel_name AS personnelName,
	pm_personnel.department_code AS departmentCode,
	pm_department.department_name AS departmentName,
IF
	(
	find_in_set( pm_department.p_department_no, @pnos ) > 0,
	@pnos := concat( @pnos, ',', pm_department.department_no ),
IF
	( pm_department.department_no = @pnos, @pnos, 0 ) 
	) AS ischild,
	@pnos AS pno 
FROM
	pm_personnel
	LEFT JOIN pm_department ON pm_personnel.department_code = pm_department.department_no
	LEFT JOIN sys_code_base ON pm_personnel.position_code = sys_code_base.code_no
	LEFT JOIN sys_user ON pm_personnel.telephone = sys_user.username 
	) temp 
WHERE
	1 = 1 
	AND temp.ischild != '0'
ORDER BY
	temp.id,
	temp.departmentCode;

效果图

sql效果图

查询出当前级以及所有子级主要的sql语句是这句：

IF
	(
	find_in_set( pm_department.p_department_no, @pnos ) > 0,
	@pnos := concat( @pnos, ',', pm_department.department_no ),
IF
	( pm_department.department_no = @pnos, @pnos, 0 ) 
	) AS ischild,

在mysql中执行成功的查询语句（对单个部门表查询）

-- 定义变量并赋值为下面sql使用，【'hulianwang'】为代码传过来的值，改为你的即可。

SET @pnos := 'hulianwang';
SELECT
	id,
	department_no,
	p_department_no,
	departmentName 
FROM
	(
SELECT
	id,
	pm_department.department_no,
	pm_department.p_department_no,
	pm_department.department_name AS departmentName,
IF
	(
	find_in_set( pm_department.p_department_no, @pnos ) > 0,
	@pnos := concat( @pnos, ',', pm_department.department_no ),
IF
	( pm_department.department_no = @pnos, @pnos, 0 ) 
	) AS ischild,
	@pnos AS pno 
FROM
	pm_department 
	) temp 
WHERE
	1 = 1 
	AND temp.ischild != '0' 
ORDER BY
	temp.id,
	temp.department_no;

效果图

sql效果图

写在Mybatis中的sql


  <isNotEmpty  property="departmentCode">
        SET @pnos := #departmentCode# ;
  </isNotEmpty>

SELECT
      id,
      personnelName,
      departmentCode,
      departmentName,
			p_department_no
      FROM
      (
      SELECT
      pm_personnel.id,
      pm_personnel.personnel_name AS personnelName,
      pm_personnel.department_code AS departmentCode,
      pm_department.department_name AS departmentName,
	  pm_department.p_department_no,
      <isNotEmpty prepend="," property="departmentCode">
        IF
        ( find_in_set( pm_department.p_department_no, @pnos ) > 0, @pnos := concat( @pnos, ',', pm_department.department_no ),if (pm_department.department_no = @pnos,@pnos,0) ) AS ischild,
        @pnos AS pno
      </isNotEmpty>

FROM
			
      pm_personnel
      LEFT JOIN pm_department ON pm_personnel.department_code = pm_department.department_no
      LEFT JOIN sys_code_base ON pm_personnel.position_code = sys_code_base.code_no
      LEFT JOIN sys_user ON pm_personnel.telephone = sys_user.username
      ) temp
WHERE 1=1
      <isNotEmpty prepend="AND" property="departmentCode">
        temp.ischild!= '0'
      </isNotEmpty>
      ORDER BY temp.id, temp.departmentCode
      LIMIT $startNumber$, $limit$

参考博客链接
https://www.cnblogs.com/zwh0910/p/16791555.html