数据库环境: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””