oracle数据库树状结构,[Oracle]生成树状结构的SQL

下面我们以PF_MENU这张表当作我们的数据表。

1、首先，我们查得整个表的数据：

//CDID：菜单ID； FCDID：父菜单ID；CDM：菜单名称。 FCDID=0是最高级菜单；CDM=- 是 分割线

SELECT CDID,FCDID,CDM FROM PF_MENU

得到的数据，如下(以下列举部分数据，不是完整的)：

CDID FCDID CDM

3002 30000 抄表册设置

9909 99000 事件代码维护

30000 0 负荷控制

30001 30000 保电控制

30003 30000 单户控制

30004 30000 遥控设置

30005 30000 剔除设置

30006 30000 功率控制

30007 30000 厂休控管理

30008 30000 下浮控投入

30010 30000 -

30011 30000 测试

30012 30000 单户日志

.......................

2、用CONNECT BY 查得树状的大致结构

SELECT CDID,CDM,FCDID

FROM PF_MENU

WHERE CDM<>'-'

CONNECT BY FCDID= PRIOR CDID START WITH  FCDID='0'

查得数据(部分数据)：

CDID CDM FCDID

90000 有序用电 0

900002 有序用电指标 90000

77999 demo1 90000

99000 辅助管理 0

9909 事件代码维护 99000

99004 时段维护 99000

99005 数据项维护 99000

99006 定时任务维护 99000

99007 群组维护 99000

80000 档案管理 0

800001 SIM卡资产管理 80000

800002 终端资产管理 80000

800004 终端运行档案管理 80000

800008 档案维护 80000

800009 终端参数设置 80000

.............................

3、2中查出的数据虽然具备了初步的树状结构，但CDID没有从小到大排序(CDID是Number型的)，下面我们将用ORDER SIBLINGS BY来排序。

SELECT CDID,CDM,FCDID

FROM PF_MENU

WHERE CDM<>'-'

CONNECT BY FCDID= PRIOR CDID START WITH  FCDID='0'

ORDER SIBLINGS BY CDID

查得数据(部分数据)：

CDID CDM FCDID

30000 负荷控制 0

3002 抄表册设置 30000

30001 保电控制 30000

30003 单户控制 30000

30004 遥控设置 30000

30005 剔除设置 30000

30006 功率控制 30000

30007 厂休控管理 30000

30008 下浮控投入 30000

30011 测试 30000

30012 单户日志 30000

50000 综合查询 0

50003 日志查询 50000

50004 终端操作记录查询 50000

60000 需求侧管理 0

.......

4、在修改一个菜单时，它的上级菜单不能为自己，更不能为自己的子菜单，以下就是不显示自己和子菜单的查询。

//以下 990001 为 个人信息维护的菜单，它是系统维护的子菜单，同时又拥有自己的2个菜单

//当我们编辑990001菜单时，不能在父菜单选择中出现自己或者自己的子菜单

//以下语句对于自己的父菜单为0的菜单是没有效果的，所以为了达到真实的效果，我们需要加一个根菜单为：0  根菜单 -1，也就是说最高级菜单是-1的菜单，而且CDID=0的菜单只有一个

SELECT CDID,CDM,FCDID

FROM PF_MENU

WHERE CDM<>'-'

CONNECT BY FCDID= PRIOR CDIDAND CDID<>'990001' START WITH  FCDID='0'

ORDER SIBLINGS BY CDID

5、3中的数据已经很清晰了，我们继续把它的CDM用目录结构显示呢？我们需要用到Level属性，这是树状结构特有的

//以下假设我们的菜单最多只有5个级别

//我们用Lead函数来取得下一行菜单的Level2，自己的Level为Level1，Level2与Level1的关系我们用Decode来处理

//最后行的Level2位NULL，

//Level2>Level1:说明自己有子菜单，

//Level2=Level1：说明下面还有同级别的菜单

//Level2

SELECT CDID,FCDID,

DECODE( NVL(LEVEL2,0),

0,SUBSTR('││││││',1,LEVEL1 - 1)||'└',

DECODE(LEVEL2 - LEVEL1,

0,SUBSTR('││││││',1,LEVEL1 - 1)||'├',

1,SUBSTR('││││││',1,LEVEL1 - 1)||'├',

SUBSTR('││││││',1,LEVEL1 - 1)||'└'

)

)||CDM CDM

FROM (

SELECT CDID,CDM,FCDID,

LEVEL LEVEL1,LEAD(LEVEL,1)  OVER (ORDER BY ROWNUM) LEVEL2

FROM PF_MENU

WHERE CDM<>'-'

CONNECT BY FCDID= PRIOR CDID START WITH  FCDID='0'

ORDER SIBLINGS BY CDID

)

最后查得数据(部分数据)：

├负荷控制

│├抄表册设置

│├保电控制

│├单户控制

│├遥控设置

│├剔除设置

│├功率控制

│├厂休控管理

│├下浮控投入

│├测试

│└单户日志

├综合查询

│├日志查询

│└终端操作记录查询

├需求侧管理

...

最终效果图，我们把它放到一个SELECT控件中显示的结果：

-----------------------------------------Juwuyi 2006-03-08