<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhyc.module.common.mapper.DeptMapper">
    
    <resultMap type="Dept" id="DeptResult">
       <result property="deptId"      column="dept_id"      />
        <result property="deptName"    column="dept_name"    />
        <result property="parentId"   column="parent_id"   />
        <result property="orderNum"   column="order_num"   />
        <result property="leader"     column="leader"     />
        <result property="status"     column="status"     />
        <result property="delFlag"    column="del_flag"    />
    </resultMap>

    <select id="selectTopSecondLevelDept" resultMap="DeptResult" parameterType="Long">
        SELECT d2.*
        FROM sys_dept d1
                 LEFT JOIN sys_dept d2
                           ON d2.dept_id = CASE
                                               WHEN d1.ancestors = '0' OR d1.parent_id = 0 THEN d1.dept_id
                               -- 二级部门：逗号只出现1次，返回自身ID
                                               WHEN LENGTH(d1.ancestors) - LENGTH(REPLACE(d1.ancestors, ',', '')) = 1
                                                   THEN d1.dept_id
                               -- 三级及以上：取第3个值（索引2）即二级部门ID
                                               ELSE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(d1.ancestors, ',', 3), ',', -1) AS UNSIGNED)
                               END
        WHERE d1.dept_id = #{deptId}
          AND d2.dept_id IS NOT NULL
    </select>

</mapper>