SQL 格式

SELECT * FROM TABLE WHERE [...结果过滤语句]START WITH [...递归开始条件]CONNECT BY PRIOR [...递归执行条件]

查询所有下级

SELECT * FROM TABLESTART WITH [ID in (‘1‘,‘2‘)]CONNECT BY PRIOR [ID = ParentId]

查询所有上级

SELECT * FROM TABLESTART WITH [ID in (‘1‘,‘2‘)]CONNECT BY PRIOR [ParentId = ID ]

注意：若出现重复项，可以使用 DISTINCT 进行去重

实例

<!-- SQL --><select id="getReportGroupTree" resultMap="SysFunctionRm"> select distinct "ID", "FUNCTION_NAME", "ICON", "FUNCTION_URL", "EXPLAINATION", "IS_DISABLED", "PARENT_FUNCTION_ID", "ORDER_CODE", "ORDE_RHIERARCHY_CODE", "SYSTEM_ID", "IS_DEFAULT", "IS_NEW_WIN", "FUNCTION_CODE", "FUNCTION_TYPE", "PRINT_SCHEME", "FUNCTION_MODE", "IS_PRINT" from SYS_FUNCTION WHERE function_Type = 2 start with ID In <foreach collection="reportGroupIds" item="id" open="(" close=")" separator="," index="index"> #{id} </foreach> connect by prior PARENT_FUNCTION_ID=ID</select>