db2的树形结构查询

数据库环境：db2

数据库结构：

<table ds="sms" name="SYS_MENU_ITEM" >
  <!-- 菜单ID  -->
  <column name="MENUITEMID">MENUITEMID</column>
  <!-- 菜单中文名 -->
  <column name="MENUITEMTITLE">MENUITEMTITLE</column>
  <!-- 菜单父节点ID -->
  <column name="PARENTID" >PARENTID</column>
  <!-- 同一目录下菜单排序ID -->
  <column name="SORTNUM">SORTNUM</column>
</table>

查询拼接语句：

 WITH n(MENUITEMID,PARENTID, chain) AS
           (SELECT MENUITEMID, PARENTID,CAST(MENUITEMTITLE AS VARCHAR(230))
               FROM SYS_MENU_ITEM b
               WHERE b.MENUITEMTITLE like '客%'  and b.menuitemid not in (select a.menuitemid from SYS_MENU_ITEM a where a.parentid=b.menuitemid)
             UNION ALL
             SELECT nplus1.MENUITEMID, nplus1.PARENTID,
                 nplus1.MENUITEMTITLE || '----' ||     n.chain
               FROM SYS_MENU_ITEM as nplus1, n
               WHERE n.PARENTID = nplus1.MENUITEMID)
 SELECT chain,MENUITEMID FROM n b where parentid=0;

从父亲节点遍历查询树形结构的数据库表，根据输入的关键字进行过滤，如查询“客”，将返回相应的“一级---二级---三级---客XX””