递归查询当前数据的所有父节点数据

达梦数据库

@Select("SELECT id\n" +
        "FROM org_organ\n" +
        "START WITH id = #{id}\n" +
        "CONNECT BY PRIOR parentid = id;")
List<String> selectParentID(@Param("id")String id);

Sqlserver数据库

@Select("WITH OrganCTE AS ( " +
        "   SELECT id, parentid " +
        "   FROM org_organ " +
        "   WHERE id = #{id} " +
        "   UNION ALL " +
        "   SELECT o.id, o.parentid " +
        "   FROM org_organ o " +
        "   INNER JOIN OrganCTE c ON o.id = c.parentid " +
        ") " +
        "SELECT id " +
        "FROM OrganCTE;")
List<String> selectParentIDForSqlserver(@Param("id")String id);

递归查询当前数据的所有子节点数据

达梦数据库

@Select("SELECT id " +
        "FROM org_organ " +
        "START WITH id = #{id} AND deletemark = 0 " +
        "CONNECT BY PRIOR id = parentid AND deletemark = 0")
List<String> selectChildID(String id);