多层级联数据的查询SQL

Love The Way You Lie 2024-03-23 20:44 13阅读 0赞

> 有如下需求：
> 
>  *  部门数据为三级及三级以上的级联数据
>  *  某个部门的用户登录系统，需要看到其部门及所有子部门的相关数据

以下：采用一个简单的例子实现上述需求，仅供参考

### 一、数据表 ###

#### 1. 部门表 ####

![在这里插入图片描述][ee542276a6f1481495a299da01c06cca.png]

#### 2. 用户表 ####

![在这里插入图片描述][97a375d623864cf49e20b5cf941cf695.png]

样例中，部门数据总共有4级。用户表中有dept\_id字段与部门表中的id关联。

梳理一下用户与部门的对应关系：  
![在这里插入图片描述][ea417e0708a447f984cd6458a80b2a1e.png]

### 二、查询SQL ###

> dept\_id 为登录用户的所属部门id

SELECT
    	* 
    FROM
    user 
    WHERE
    	dept_id IN (
    		( SELECT id FROM dept WHERE id = #{dept_id} ) UNION ALL
    		(
    		SELECT
    			id 
    		FROM
    			(
    			SELECT
    				t1.id,
    				t1.NAME,
    			IF
    				( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
    			FROM
    				( SELECT id, parent_id, NAME FROM dept t ORDER BY parent_id, id ) t1,
    				( SELECT @pids := #{dept_id} ) t2 
    			) t3 
    		WHERE
    			ischild != 0 
    		) 
    	)

### 三、测试用例 ###

#### 1. 用户 “张三” 登录，会看到所有部门数据 ####

![在这里插入图片描述][9f4954195eb548ceae839da0066edbe8.png]

#### 2. 用户 “王五” 登录，只能看到他自己的数据 ####

![在这里插入图片描述][79f9c5518c794290ad8361d165f88fed.png]

#### 3. 用户 “周武” 登录，看到洪山区及洪山区所有子部门的用户 ####

![在这里插入图片描述][685090cc2b434187bc63b6ffab256af5.png]

[ee542276a6f1481495a299da01c06cca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/73b0cf83ee314c0194db4c418fd86b52.png
[97a375d623864cf49e20b5cf941cf695.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/7124e108f7bf44f283379dc7a022d51c.png
[ea417e0708a447f984cd6458a80b2a1e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/4795f13194d94dea977200c85dcaa806.png
[9f4954195eb548ceae839da0066edbe8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ba7aeba4fefd4daa97c12aac0525d90e.png
[79f9c5518c794290ad8361d165f88fed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ab6a59898bef4e498e2417372d0de4e1.png
[685090cc2b434187bc63b6ffab256af5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/453c64abedd64f59a822f93b3cbdd04d.png