部门及子部门查询

CREATE TABLE sys_dept (
id bigint NOT NULL COMMENT ‘ID’,
parent_id bigint NOT NULL COMMENT ‘父级部门ID’,
name varchar(64) NOT NULL COMMENT ‘名称’,
full_name varchar(512) NOT NULL COMMENT ‘全名’,
full_id varchar(512) DEFAULT ‘’ COMMENT ‘部门全路径id’,
level int NOT NULL COMMENT ‘层级’,
PRIMARY KEY (id),
KEY idx_sys_dept_leader_id (leader_id) USING BTREE,
KEY idx_sys_dept_sync_target_parent_code (sync_target_parent_code),
KEY idx_sys_dept_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=‘部门’

CREATE TABLE sys_dept_scope (
parent_dept_id bigint NOT NULL COMMENT ‘父级部门ID’,
children_dept_id bigint NOT NULL COMMENT ‘子级部门ID’,
parent_dept_level smallint DEFAULT NULL COMMENT ‘父级部门层级’,
children_dept_level smallint DEFAULT NULL COMMENT ‘子级部门层级’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
PRIMARY KEY (parent_dept_id,children_dept_id),
KEY idx_sys_dept_scope_children_dept_id (children_dept_id,parent_dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=‘部门关联范围’

select
*
from
sys_dept sd
where
exists(
select
1
from
sys_dept_scope sdc
where
sdc.parent_dept_id in (516238236490661888)
and sdc.children_dept_id = sd.id
limit 1
)