DB2 递归
DB2反向递归拿到所有部门
WITH ROOT ( CODE, CUSTOM_CODE, FULL_NAME, ID, NAME, PARENT_CODE, PY_CODE, LEVEL, PARENT_NAME) AS ( SELECT A.CODE, A.CUSTOM_CODE, A.FULL_NAME, A.ID, A.NAME, A.PARENT_CODE, A.PY_CODE, A.LEVEL, A.PARENT_NAME FROM VIEW_INST_DEPART_CLASSIFY_LEVEL A WHERE A.CODE IN ( SELECT DISTINCT B.DEPARTMENT_CODE FROM MEDICAL_WORKER B WHERE 1=1 AND B.INST_CODE= '467223275' ) UNION ALL SELECT A.CODE, A.CUSTOM_CODE, A.FULL_NAME, A.ID, A.NAME, A.PARENT_CODE, A.PY_CODE, A.LEVEL, A.PARENT_NAME FROM VIEW_INST_DEPART_CLASSIFY_LEVEL a, ROOT R WHERE A.CODE = R.PARENT_CODE ) SELECT DISTINCT CODE, CUSTOM_CODE, FULL_NAME, ID, NAME, PARENT_CODE, PY_CODE, LEVEL, PARENT_NAME FROM ROOT
第二个 SELECT 语句执行多次。将种子作为输入(JOIN 中的辅助表)传递给第二个 SELECT 语句以产生下一个行集合。将 JOIN 的结果添加(UNION ALL)到虚拟表的当前内容中,并放回到其中以形成用于下一次传递的输入。只要有行产生,这个过程就会继续。